我有一些json类似于下面存储在postgres json列中的json . 我正在尝试查询它以识别一些错误输入的数据 . 我基本上都在寻找房屋描述与房屋号码相同的地址 . 我无法理解如何做到这一点 .
{
"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,它将找到数据匹配的位置:
select *
FROM title_register_data
where address_data->'schools'->0->'addresses'->0->>'house_description'=
address_data->'schools'->0->'addresses'->0->>'house_no'
这显然只适用于第一所学校的第一个地址 . 有没有办法查询每所学校的所有地址?
1 回答
在横向连接中使用
jsonb_array_elements()
的次数与要比较的元素的json数组的深度相同: