Postgresql 如何在 postgres json 列中查询嵌套数组

发布于05月20日

我在postgres json列中存储了一些类似于下面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'

这显然只适用于第一所学校的第一个地址.有没有办法查询每所学校的所有地址？

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)

Postgresql 如何在 postgres json 列中查询嵌套数组

推荐答案

Postgresql相关问答推荐

即使密码更改，也可以访问Docker Postgresql数据库

无法继承BYPASSRLS

将XML解析从T-SQL迁移到Postgres时出现问题

PL/pgSQL中的IP递增函数

正在加载 pgAdmin 4 v7.4...同时打开 pgAdmin

使用来自 docker 图像的 postgres 设置 Keycloak 21

需要用 jack/pgx 更新 golang 中复合类型的 PSQL 行

Postgres 将分区附加到表的时间太长.想明白为什么

找出两个表之间的差异

在 postgresql 数据库 timestampz 中保留用户偏移量

将 Postgres 与 Grails 一起使用

MAC OS X 上的 Postgres 权限被拒绝

如何防止用户看到其他数据库和其他数据库中的表？

SQLAlchemy 和多个进程的连接问题

如何确定 NULL 是否包含在 Postgres 的数组中？

PostgreSQL：将时间戳转换为时间 - 或仅从时间戳列中检索时间

PostgreSQL 权限解释

在 PostgreSQL 数组中查找值的位置

pg_restore 会覆盖现有的表吗？

如何从我的 postgresql 查询中获取最小值、中值和最大值？