如何将JSON数组转换为Postgres数组？

{
    "name": "foo",
    "tags": ["foo", "bar"]
}

#1 楼

Postgres 9.4或更高版本

显然受此帖子启发，Postgres 9.4添加了缺少的功能：感谢Laurence Rowe的补丁程序和Andrew Dunstan的贡献！


json_array_elements_text(json)
jsonb_array_elements_text(jsonb)

取消嵌套JSON数组。然后使用array_agg()或ARRAY构造函数从中构建一个Postgres数组。或使用string_agg()构建一个text字符串。

在LATERAL或相关子查询中每行汇总未嵌套的元素。然后将保留原始顺序，并且在外部查询中不需要ORDER BY，GROUP BY甚至是唯一键。请参阅：


如何将ORDER BY和LIMIT与聚合函数结合使用？

在以下所有SQL中将jsonb的'json'替换为'jsonb'代码。

SELECT t.tbl_id, d.list
FROM   tbl t
CROSS  JOIN LATERAL (
   SELECT string_agg(d.elem::text, ', ') AS list
   FROM   json_array_elements_text(t.data->'tags') AS d(elem)
   ) d;

短语法：

SELECT t.tbl_id, d.list
FROM   tbl t, LATERAL (
   SELECT string_agg(value::text, ', ') AS list
   FROM   json_array_elements_text(t.data->'tags')  -- col name default: "value"
   ) d;

相关：


LATERAL和PostgreSQL中的子查询有什么区别？

相关子查询中的ARRAY构造函数：

SELECT tbl_id, ARRAY(SELECT json_array_elements_text(t.data->'tags')) AS txt_arr
FROM   tbl t;

相关：


如何结合聚合函数应用ORDER BY和LIMIT？

细微差别：null元素保留在实际数组中。在上述查询中产生text字符串是不可能的，该字符串不能包含null值。真正的表示形式是数组。

函数包装器

要使重复使用更加简单，请将逻辑封装在函数中：

CREATE OR REPLACE FUNCTION json_arr2text_arr(_js json)
  RETURNS text[] LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
'SELECT ARRAY(SELECT json_array_elements_text(_js))';

使其具有SQL函数，因此可以在较大的查询中内联。
使其具有IMMUTABLE（因为它是）以避免在较大的查询中重复求值并允许其在索引表达式中使用。
将其设为PARALLEL SAFE（在Postgres 9.6或更高版本中！）以免妨碍并行化。请参见：


何时将功能标记为PARALLEL RESTRICTED与PARALLEL SAFE？

呼叫： /> db <>在这里拨弄



Postgres 9.3或更早版本

使用功能json_array_elements()。但是我们从中得到了双引号字符串。

外部查询中带有聚合的替代查询。 CROSS JOIN删除数组缺失或为空的行。对于处理元素也可能有用。我们需要一个唯一的键来聚合：

SELECT tbl_id, json_arr2text_arr(data->'tags')
FROM   tbl;

ARRAY构造函数，但仍带有引号的字符串：

SELECT t.tbl_id, string_agg(d.elem::text, ', ') AS list
FROM   tbl t
CROSS  JOIN LATERAL json_array_elements(t.data->'tags') AS d(elem)
GROUP  BY t.tbl_id;

请注意，与上述不同，null转换为文本值“ null”。从严格意义上讲是不正确的，并且可能有歧义。

穷人用trim()取消报价：

SELECT tbl_id, ARRAY(SELECT json_array_elements(t.data->'tags')) AS quoted_txt_arr
FROM   tbl t;

从tbl中检索一行：

SELECT t.tbl_id, string_agg(trim(d.elem::text, '"'), ', ') AS list
FROM   tbl t, json_array_elements(t.data->'tags') d(elem)
GROUP  BY 1;

字符串形式相关子查询：

SELECT string_agg(trim(d.elem::text, '"'), ', ') AS list
FROM   tbl t, json_array_elements(t.data->'tags') d(elem)
WHERE  t.tbl_id = 1;

ARRAY构造函数：

SELECT tbl_id, (SELECT string_agg(trim(value::text, '"'), ', ')
                FROM   json_array_elements(t.data->'tags')) AS list
FROM   tbl t;

原始的（过时的）SQL Fiddle.db <>在这里拨弄。

相关：


需要从中动态选择JSON数组元素postgresql表

注释（自pg 9.4起已过时）

我们需要json_array_elements_text(json)，这是json_array_elements(json)的孪生子，才能从JSON数组返回正确的text值。但这似乎是所提供的JSON函数库中所缺少的。或其他一些函数从标量text值中提取JSON值。我似乎也错过了那个。
所以我即兴使用trim()，但是对于非平凡的情况，这会失败...

#2 楼

PG 9.4+

肯定的答案是您所需要的，但是为了简单起见，这里我使用了一个帮助程序：

CREATE OR REPLACE FUNCTION jsonb_array_to_text_array(p_input jsonb)
 RETURNS text[]
 LANGUAGE sql
 IMMUTABLE
AS $function$

SELECT array_agg(ary)::text[] FROM jsonb_array_elements_text(p_input) AS ary;

$function$;

然后再做：

SELECT jsonb_array_to_text_array('["a", "b", "c"]'::jsonb);

更新2/23/2020以回应评论：评论是正确的，因为这样做可能更有效。在我发布时，还没有提供模块化的解决方案，所以我认真地提供了一个解决方案，即使不是最优的。从那以后，Erwin用简单高效的功能更新了他的答案，所以我从未更新过我的答案。现在更新它，因为仍然有更多人关注此答案。

又有一个更新，因为这让我有点烦：
如果没有值，上述函数将返回null。根据您的情况，这可能不是理想的。这是一个函数，如果值不是null，则返回空数组，但如果输入为null，则仍返回null。

CREATE OR REPLACE FUNCTION jsonb_array_to_text_array_strict(p_input jsonb)
 RETURNS text[]
 LANGUAGE sql
 IMMUTABLE
AS $function$

SELECT 
  CASE 
    WHEN p_input IS null 
    THEN null 
    ELSE coalesce(ary_out, ARRAY[]::text[]) 
  END
FROM (
  SELECT array_agg(ary)::text[] AS ary_out
  FROM jsonb_array_elements_text(p_input) AS ary
) AS extracted;

$function$
;

#3 楼

在PostgreSQL邮件列表上问了这个问题，我想出了一种通过JSON字段提取运算符将JSON文本转换为PostgreSQL文本类型的方法：

CREATE FUNCTION json_text(json) RETURNS text IMMUTABLE LANGUAGE sql
AS $$ SELECT ('['||||']')::json->>0 $$;

db=# select json_text(json_array_elements('["hello",1.3,"\u2603"]'));
 json_text 
-----------
 hello
 1.3
 ☃

它将值转换为单元素数组，然后要求第一个元素。

另一种方法是使用此运算符来一次提取所有字段。但是对于大型数组，这可能会更慢，因为它需要为每个数组元素解析整个JSON字符串，从而导致O（n ^ 2）复杂性。

CREATE FUNCTION json_array_elements_text(json) RETURNS SETOF text IMMUTABLE LANGUAGE sql
AS $$ SELECT ->>i FROM generate_series(0, json_array_length()-1) AS i $$;

db=# select json_array_elements_text('["hello",1.3,"\u2603"]');
 json_array_elements_text 
--------------------------
 hello
 1.3
 ☃

#4 楼

我已经测试了一些选项。这是我最喜欢的查询。假设我们有一个包含id和json字段的表。 json字段包含数组，我们希望将其转换为pg数组。

SELECT * 
FROM   test 
WHERE  TRANSLATE(jsonb::jsonb::text, '[]','{}')::INT[] 
       && ARRAY[1,2,3];

它在任何地方都可以工作，并且比其他任何地方都快，但是看起来有些残酷）

首先将json数组转换为文本，然后将方括号更改为括号。最终，文本被强制转换为所需类型的数组。

SELECT TRANSLATE('[1]'::jsonb::text, '[]','{}')::INT[];

，如果您喜欢text []数组

SELECT TRANSLATE('[1]'::jsonb::text, '[]','{}')::TEXT[];

#5 楼

这几个函数取自该问题的答案，它们是我正在使用的函数，它们在运行中效果很好。

CREATE OR REPLACE FUNCTION json_array_casttext(json) RETURNS text[] AS $f$
    SELECT array_agg(x) || ARRAY[]::text[] FROM json_array_elements_text() t(x);
$f$ LANGUAGE sql IMMUTABLE;

CREATE OR REPLACE FUNCTION jsonb_array_casttext(jsonb) RETURNS text[] AS $f$
    SELECT array_agg(x) || ARRAY[]::text[] FROM jsonb_array_elements_text() t(x);
$f$ LANGUAGE sql IMMUTABLE;

CREATE OR REPLACE FUNCTION json_array_castint(json) RETURNS int[] AS $f$
    SELECT array_agg(x)::int[] || ARRAY[]::int[] FROM json_array_elements_text() t(x);
$f$ LANGUAGE sql IMMUTABLE;

CREATE OR REPLACE FUNCTION jsonb_array_castint(jsonb) RETURNS int[] AS $f$
    SELECT array_agg(x)::int[] || ARRAY[]::int[] FROM jsonb_array_elements_text() t(x);
$f$ LANGUAGE sql IMMUTABLE;

在每个函数中，通过与一个空值连接数组，它们处理了一个让我费尽脑筋的情况，因为如果您尝试从json / jsonb投射一个空数组，如果没有它，您将一无所获，而不是像空数组（{}）期望。我敢肯定他们有一些优化，但是为了简单起见，只剩下它们了。