postgresql – 如何将json数组转换为postgres数组？

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

Postgres 9.4或更新

显然inspired by this post,Postgres 9.4添加了缺失的功能：
Thanks to Laurence Rowe for the patch and Andrew Dunstan for committing!

> json_array_elements_text(json)
> jsonb_array_elements_text(jsonb)

要取消JSON数组.然后使用array_agg()或ARRAY constructor从中构建Postgres数组.或者string_agg()构建一个文本字符串.

在LATERAL或相关子查询中每行聚合未被引用的元素.然后保留原始订单,我们不需要ORDER BY,GROUP BY甚至外部查询中的唯一键.看到：

> How to apply ORDER BY and LIMIT in combination with an aggregate function?

在以下所有SQL代码中将’json’替换为jsonb的’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;

有关：

> What is the difference between LATERAL and a subquery in PostgreSQL?

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

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

有关：

> How to apply ORDER BY and LIMIT in combination with an aggregate function?

细微差别：null元素保留在实际数组中.在上面生成文本字符串的查询中,这是不可能的,该文本字符串不能包含空值.真实的表示是一个数组.

功能包装器

为了重复使用,为了使这更简单,将逻辑封装在一个函数中：

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

使它成为SQL函数,因此在更大的查询中它可以是inlined.
使其成为IMMUTABLE(因为它是)以避免在较大的查询中重复评估并允许它在索引表达式中.

呼叫：

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

db<>fiddle在这里

Postgres 9.3或更早

使用函数json_array_elements().但我们从中获取双引号字符串.

外部查询中具有聚合的备用查询. CROSS JOIN删除缺少或空数组的行.也可用于处理元素.我们需要一个唯一的密钥来聚合：

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;

ARRAY构造函数,仍带引号字符串：

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

请注意,与上面不同,null被转换为文本值“null”.严格来说,不正确,可能含糊不清.

可怜的男人没有修剪()：

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;

从tbl检索单行：

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

字符串形成相关子查询：

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

ARRAY构造函数：

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

原创(过时)SQL Fiddle.
db<>fiddle在这里.

有关：

> Need to select a JSON array element dynamically from a postgresql table

备注(自第9.4页以来已过时)

我们需要一个json_array_elements_text(json),它是json_array_elements(json)的双胞胎,可以从JSON数组中返回正确的文本值.但这似乎从provided arsenal of JSON functions中缺失.或者其他一些从标量JSON值中提取文本值的函数.我似乎也错过了那一个.所以我用trim()即兴创作,但对于非平凡的情况,这将失败……