2

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?

4
  • Any idea? Normalize it. Commented Jul 14, 2014 at 13:04
  • "column named json of datatype json" it is a bad idea to name a column with its type name. Commented Jul 14, 2014 at 14:15
  • 1
    You can try to use full text search on the whole JSON but this idea is not that good. You can even try to build full text search on subset of the JSON but it is rather hard. The best way to handle it - normalize and use FTS of specific columns. Commented Jul 14, 2014 at 14:18
  • 1
    PostgreSQL can't index that usefully - or at least not without some ugly hacks with expression indexes. 9.4 will be able to, with jsonb. Commented Jul 14, 2014 at 15:31

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.