PostgreSQL, get JSON object field based on a the value of a parallel attribute

0
votes

Suppose we are dealing with a JSON object where there can be multiple child nodes with the same structure, and we want to get the value of attribute B,C,D,etc. where attribute A equals a specific value. Below is an example.

{
"addresses": [{
    "type": "home",
    "address": "123 fake street",
    "zip": "24301"
}, {
    "type": "work",
    "address": "346 Main street",
    "zip": "24352"
}, {
    "type": "PO Box",
    "address": "PO BOX 132, New York, NY",
    "zip": "10001"
}, {
    "type": "second",
    "address": "1600 Pennsylvania Ave.",
    "zip": "90210"
}]}

Is there any JSON operator in PostgreSQL where I can get the zip code, where the address type is "work" or "home"? I am looking at https://www.postgresql.org/docs/current/static/functions-json.html and not finding what I'm looking for.

1
jsonpostgresql

1 Answers

2
votes

You need to "unnest" (i.e. normalize) the data, then you can apply a WHERE condition on it:

select t.adr ->> 'zip', t.adr ->> 'address'
from the_table
  cross join lateral jsonb_array_elements(the_column -> 'addresses') as t(adr)
where t.adr ->> 'type' in ('work', 'home');

Online example: http://rextester.com/TDB99535