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?

klin · Accepted Answer · 2023-05-16 11:00:01Z

73

Use jsonb_array_elements() in a 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)

edited May 16, 2023 at 11:00

answered May 19, 2017 at 17:31

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

spannerj Over a year ago

Excellent thank you. That is what I was after. I did tweak it slightly and used json_array_elements as it isn't a jsonb column but the principle is the same.

Ibrahim Over a year ago

I would love to give you a double vote up, but that's not possible :)

fei0x Over a year ago

This strips records whose schools array is empty (assuming no where clause). Do we have an elegant way to keep those records? (and keep the selected values as null?)

klin Over a year ago

@fei0x - Use left join ... on true, see db<>fiddle.

fei0x Over a year ago

I can't say I totally understand why, but that definitely does the trick. Thank you.

|

Collectives™ on Stack Overflow

How to query nested arrays in a postgres json column?

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related