sql - 如何“解包”存储在 PostgreSQL 数据库中的 JSON 数组？

Question

我有一个带有 json 列的 postgresql 数据库，以及一些普通的列。

json 列存储一个对象数组，如下所示：

[
   {"status": "REGD", "register": "EYR", "start_date": "2008-09-01"}, 
   {"status": "REGD", "register": "CCR", "start_date": "2008-09-01"}, 
   {"status": "REGD", "register": "VCR", "start_date": "2008-09-01"}
]

我想SELECT在这个表上做一个查询，分解数组，这样每个都是它自己的行，比如：

| status  | register  | start_date   |
--------------------------------------
| "REGD"  | "EYR"     | "2008-09-01" |
| "REGD"  | "CCR"     | "2008-09-01" |
| "REGD"  | "VCR"     | "2008-09-01" |

我该怎么做？我已经尝试过运算符->>and ->，但我没有看到像这样解开数组的简单方法。

看起来一些 postgres 的json 函数会有所帮助，但我正在努力将它们应用于这种情况。

mongodb 有一个展开操作。我想我正在寻找类似的东西（已编辑）

---

更新：我有一些东西正在使用json_array_elements，但我仍在努力将每个键解压缩到自己的列中。

这不太管用：

SELECT
    jsonb_array_elements(registers) AS register
    register->>'register' AS register_name

FROM items

标签： sqljsonpostgresql

Answer 1

在这里mytable，CTE 模仿您现有的表格another_column进行说明。

with mytable(another_column, j) as 
(
 select 'other values', 
 '[
   {"status": "REGD", "register": "EYR", "start_date": "2008-09-01"}, 
   {"status": "REGD", "register": "CCR", "start_date": "2008-09-01"}, 
   {"status": "REGD", "register": "VCR", "start_date": "2008-09-01"}
 ]'::json
)
select another_column, 
       jae ->> 'status' status,
       jae ->> 'register' register,
       jae ->> 'start_date' start_date 
  from mytable 
 cross join lateral json_array_elements(j) jae;