How to query nested arrays in a postgres json column?

Question

I have some json similar to the json below stored in a postgres json column. I'm trying query it to identify some incorrectly entered data. I'm basically looking for addresses where the house description is the same as the house number. I can't quite work out how to do it.

{   "timestamp": "2014-10-23T16:15:28+01:00",   "schools": [     {     "school_id": "1",     "addresses": [       {         "town": "Birmingham",         "house_description": "1",         "street_name": "Parklands",         "addr_id": "4",         "postcode": "B5 8KL",         "house_no": "1",         "address_type": "UK"       },       {         "town": "Plymouth",         "house_description": "Flat a",         "street_name": "Fore Street",         "addr_id": "2",         "postcode": "PL9 8AY",         "house_no": "15",         "address_type": "UK"       }     ]   },   {     "school_id": "2",     "addresses": [       {         "town": "Coventry",         "street_name": "Shipley Way",         "addr_id": "19",         "postcode": "CV8 3DL",         "house_no": "662",         "address_type": "UK"       }     ]   }   ] } 

I have written this sql which will find where the data matches:

select * FROM title_register_data where address_data->'schools'->0->'addresses'->0->>'house_description'=  address_data->'schools'->0->'addresses'->0->>'house_no' 

This obviously only works on the first address on the first school. Is there a way of querying all of the addresses of every school?

Answer 1

Use jsonb_array_elements() in lateral join as many times as the depth of a json array which elements you want to compare:

select      schools->>'school_id' school_id,     addresses->>'addr_id' addr_id,     addresses->>'house_description' house_description,     addresses->>'house_no' house_no from title_register_data, jsonb_array_elements(address_data->'schools') schools, jsonb_array_elements(schools->'addresses') addresses where addresses->>'house_description' = addresses->>'house_no';   school_id | addr_id | house_description | house_no  -----------+---------+-------------------+----------  1         | 4       | 1                 | 1 (1 row)