时间戳

首页 > 解决方案 > 无法从 JSON_ARRAY_ELEMENTS() 获取所有值

json - 无法从 JSON_ARRAY_ELEMENTS() 获取所有值

问题描述

带有样本数据的表格：

create table tbl_jsdata
(
id int,
p_id int,
field_name text,
field_value text 
);

insert into tbl_jsdata values       
(1,101,'Name','Sam'),
(2,101,'City','Dubai'),
(3,101,'Pin','1235'),
(4,101,'Country','UAE'),
(5,102,'Name','Sam'),
(6,102,'City','Dubai'),
(7,102,'Name','Sam Jack'),
(8,102,'Name','Test'),
(9,102,'Name',null);

json_agg 查询：

drop table if exists tempJSData;

select p_id,
    json_build_array(json_object_agg(field_name, field_value)) into tempJSData
from tbl_jsdata
group by p_id;

得到结果：

select p_id,(json_array_elements(json_build_array)->>'Name')::text Namess
from tempJSData


p_id    Namess
---------------------------------
101     Sam
102

预期结果：

p_id    Namess
---------------------------------
101     Sam
102     Sam
102     Sam Jack
102     Test
102

标签： jsonpostgresqlpostgresql-10

解决方案

我认为这是因为您没有创建Name.

如果您检查您的查询

select p_id,
    json_build_array(json_object_agg(field_name, field_value))
from tbl_jsdata
group by p_id;

结果是

 p_id |                                      json_build_array
------+---------------------------------------------------------------------------------------------
  101 | [{ "Name" : "Sam", "City" : "Dubai", "Pin" : "1235", "Country" : "UAE" }]
  102 | [{ "Name" : "Sam", "City" : "Dubai", "Name" : "Sam Jack", "Name" : "Test", "Name" : null }]
(2 rows)

具有该Name字段的多个相邻条目。以下json_array_elements(json_build_array)->>'Name'将仅获取第一个条目。我建议首先基于p_id和创建一个数组field_name

with array_built as (
    select p_id,field_name,
        array_agg(field_value) field_value
    from tbl_jsdata
    group by p_id, field_name
)
select p_id,
    jsonb_object_agg(field_name, field_value)
from array_built
group by p_id
;

结果可以优化，因为即使只有一个值，它也会创建一个数组

 p_id |                             jsonb_object_agg
------+---------------------------------------------------------------------------
  101 | {"Pin": ["1235"], "City": ["Dubai"], "Name": ["Sam"], "Country": ["UAE"]}
  102 | {"City": ["Dubai"], "Name": ["Sam", "Sam Jack", "Test", null]}
(2 rows)

但是现在您可以正确解析它整个查询是

select p_id,
    json_build_array(json_object_agg(field_name, field_value)) 
from tbl_jsdata
group by p_id;

select p_id,
    json_build_array(json_object_agg(field_name, field_value)) 
from tbl_jsdata
group by p_id;

with array_built as (
    select p_id,field_name,
        array_agg(field_value) field_value
    from tbl_jsdata
    group by p_id, field_name
), agg as (
select p_id,
    jsonb_object_agg(field_name, field_value) json_doc
from array_built
group by p_id
)

select p_id, jsonb_array_elements(json_doc->'Name')  from agg;
;

预期结果为

 p_id | jsonb_array_elements
------+----------------------
  101 | "Sam"
  102 | "Sam"
  102 | "Sam Jack"
  102 | "Test"
  102 | null
(5 rows)

推荐阅读