I have data for customers with more than one adresses with json representation like this:
{
"firstName" : "Max",
"lastName" : "Mustermann",
"addresses" : [{
"city" : "München",
"houseNumber" : "1",
"postalCode" : "87654",
"street" : "Leopoldstraße",
}, {
"city" : "Berlin",
"houseNumber" : "2a",
"postalCode" : "12345",
"street" : "Kurfürstendamm",
}
]
}
these json is stored in a column named json of datatype json in a table named customer.
I want to query like this:
SELECT *
FROM customer cust,
json_array_elements(cust.json#>'{addresses}') as adr
WHERE adr->>'city' like '%erlin'
and adr->>'street' like '%urf%';
Query works fine ... but can't create index that postgresql 9.3.4 can use.
Any idea?
jsonb
.