时间戳

首页 > 解决方案 > 从 JSON Array 字段中获取选定的列列表

arrays - 从 JSON Array 字段中获取选定的列列表

问题描述

我有下表包含示例数据：

表：

create table tbl_jtest
(
    jdata json
);

insert into tbl_jtest values
('[{"fdate":"2021-01-01","name":"John","add1":"UK"},{"name":"Sam"},{"fdate":"2020-12-12","number":89789}]'),
('[{"name":"Jack","add2":"US","number":12302}]'),
('[{"fdate":"2011-02-11","name":"Lee"},{"number":785412}]'),
('[{"fdate":"2021-06-05","name":"Smith"},{"fdate":"2015-12-10","name":"Will"},{"name":"Jazz","number":556644}]');

我有需要从 JSON Array 列中选择的输入列列表jdata。

给定的列列表（可能是一个或多个）：

列列表：fdate,name,number

预期输出：

jdata
-------------------------------------------------------------------------------------------
[{"fdate":"2021-01-01","name":"John"},{"name":"Sam"},{"fdate":"2020-12-12","number":89789}]
[{"name":"Jack","number":12302}]
[{"fdate":"2011-02-11","name":"Lee"},{"number":785412}]
[{"fdate":"2021-06-05","name":"Smith"},{"fdate":"2015-12-10","name":"Will"},{"name":"Jazz","number":556644}]

标签： arraysjsonpostgresqlpostgresql-11

解决方案

演示：db<>小提琴

SELECT
    id,
    json_agg(new_object)                                                   -- 5
FROM (
    SELECT
        id,
        json_object_agg(elems.key, elems.value) as new_object              -- 4
    FROM tbl_jtest,
        json_array_elements(jdata) WITH ORDINALITY a_elems(value, index),  -- 1 
        json_each(a_elems.value) elems                                     -- 2
    WHERE elems.key IN ('fdate', 'name', 'number')                         -- 3
    GROUP BY id, a_elems.index
) s
GROUP BY id

您需要提取并展开整个 JSON 对象，以检查键字符串并再次重新聚合过滤后的记录：

  1. 将数组提取到每个 JSON 对象的一条记录中。添加了一个索引，WITH ORDINALITY以便以后能够在重新聚合时识别正确的元素。
  2. 将每个 JSON 对象的元素提取到一行中。这将创建列key和value
  3. 过滤相关的关键字符串
  4. 用剩余的key/value对重建 JSON 对象
  5. 将它们重新聚合到一个新的 JSON 数组中。

推荐阅读