如何在postgres json列中查询嵌套数组? [英] How to query nested arrays in a postgres json column?

问题描述

我在postgres json列中存储了一些类似于下面的json的json.我正在尝试查询它以识别一些错误输入的数据.我基本上是在寻找房屋描述与房屋号码相同的地址.我不知道该怎么做.

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"
      }
    ]
  }
  ]
}

我已经编写了此sql，它将查找数据匹配的地方:

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?

推荐答案

在横向联接中使用jsonb_array_elements()的次数与要比较元素的json数组的深度相同:

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)  

这篇关于如何在postgres json列中查询嵌套数组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！