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

概述我有一个列数据,其中包含一个大致如下的json文档： { "name": "foo", "tags": ["foo", "bar"]} 我想将嵌套的tags数组转换为连接字符串(foo,bar).理论上,使用array_to_string()函数很容易实现这一点.但是,此函数不会对json数组起作用.所以我想知道如何将这个json数组转换为Postgres数组？ Postgres 我有一个列数据,其中包含一个大致如下的Json文档：

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

我想将嵌套的Tags数组转换为连接字符串(foo,bar).理论上,使用array_to_string()函数很容易实现这一点.但是,此函数不会对Json数组起作用.所以我想知道如何将这个Json数组转换为Postgres数组？

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)
> @L_502_3@

要取消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.ListFROM   tbl tCROSS  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.ListFROM   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_arrFROM   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 ListFROM   tbl tCROSS  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_arrFROM   tbl t;

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

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

SELECT t.tbl_ID,string_agg(trim(d.elem::text,'"'),') AS ListFROM   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 ListFROM   tbl t;

ARRAY构造函数：

SELECT tbl_ID,ARRAY(SELECT trim(value::text,'"')                     FROM   Json_array_elements(t.data->'Tags')) AS txt_arrFROM   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()即兴创作,但对于非平凡的情况,这将失败……

总结

以上是内存溢出为你收集整理的postgresql – 如何将json数组转换为postgres数组？全部内容，希望文章能够帮你解决postgresql – 如何将json数组转换为postgres数组？所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。