主页 / dba / 问题 / 54283
Accepted
Christoph
Asked: 2013-12-03 12:48:12 +0800 CST

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

  • 772

我有一个data类型的列，json其中包含这样的 JSON 文档：

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

我想将嵌套tags数组转换为串联字符串 ( 'foo, bar')。这array_to_string()在理论上很容易实现。但是，此函数不接受json输入。所以我想知道如何将这个 JSON 数组转换为 Postgres 数组（类型text[]）？

postgresql postgresql-9.3

7 个回答

  1. Best Answer

    Postgres 9.4 或更高版本

    受这篇文章的启发，Postgres 9.4 将缺失的函数添加到了非嵌套 JSON 数组中。
    感谢 Laurence Rowe 的补丁和 Andrew Dunstan 的承诺！

    使用array_agg()或ARRAY构造函数从. _ 或者用值列表（类型）构建一个字符串。text[]text
    string_agg()text

    专注于数组输出 ( text[])，而不是字符串 ( text)。重要区别：null元素保存在实际数组中。null这在不能包含值的字符串中是不可能的。真正的表示是一个数组。

    将 'jsonb' 替换为 'json' 以输入json以下所有 SQL 代码。

    TLDR：使用自定义函数

    将逻辑封装在一个函数中以供重复使用：

    CREATE OR REPLACE FUNCTION jsonb_array_to_text_array(_js jsonb)
  RETURNS text[]
  LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS
'SELECT ARRAY(SELECT jsonb_array_elements_text(_js))';

    称呼：

    SELECT tbl_id, jsonb_array_to_text_array(data->'tags')
FROM   tbl;
    • LANGUAGE sql对于简单的功能。（在我使用 Postgres 14 的最新测试中最​​快。）
    • IMMUTABLE（因为它是）避免在更大的查询中重复评估并允许在索引表达式中使用它。
    • PARALLEL SAFE（在Postgres 9.6或更高版本中！）允许在大查询中并行执行。看：
    • STRICT返回null输入null。另外：更快。由于 ARRAY 构造函数，该函数无论如何都不能内联STRICT，所以不能损害它。

    这个带有STRICT修饰符的函数也尽可能地忠实于原始函数，因为它null为null输入返回，为空数组输入返回一个空数组。比以下所有查询都要好。

    为了完整性：to_jsonb()用于反向 SQL 数组 →jsonb转换。

    各种解决方案，一步一步

    立即聚合或相关子查询中的每行，然后保留原始顺序，我们不需要，甚至外部查询中的唯一键。看：LATERALORDER BYGROUP BY

    基本查询，返回null空数组或null输入：

    SELECT t.tbl_id, d.txt_arr
FROM   tbl t
CROSS  JOIN LATERAL (
   SELECT array_agg(d.elem) AS txt_arr
   FROM   jsonb_array_elements_text(t.data->'tags') AS d(elem)
   ) AS d;

    简短的语法，返回null空数组或null输入：

    SELECT t.tbl_id, d.txt_arr
FROM   tbl t, LATERAL (
   SELECT array_agg(value) AS txt_arr
   FROM   jsonb_array_elements_text(t.data->'tags')  -- default name is "value"
   ) d;

    使用 ARRAY 构造函数更短（更快），为空数组或null输入返回空数组：

    SELECT t.tbl_id, t.data->'tags' AS jsonb_arr, d.txt_arr
FROM   tbl t, LATERAL (
   SELECT ARRAY(SELECT jsonb_array_elements_text(t.data->'tags'))
   ) d(txt_arr);

    使用相关子查询甚至更短（更快），为空数组或null输入返回空数组：

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

    db<>在这里摆弄

    以上所有内容都保留了元素的原始顺序。


    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”。不正确，严格来说，并且可能模棱两可。

    穷人不引用trim()：

    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, '"'), ', ') AS list
FROM   tbl t, 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;

    数组构造函数：

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

    db<>fiddle here
    旧sqlfiddle

    有关的：

    原始注释（自第 9.4 页起已过时）

    我们需要一个json_array_elements_text(json), 的双胞胎从 JSON 数组json_array_elements(json)返回正确的值。text但这似乎从提供的 JSON 函数库中消失了。text或其他一些从标量值中提取值的json函数。我好像也错过了那个。
    所以我即兴创作trim()，但对于非平凡的案例将失败......

    • 157

  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);

    2020 年 2 月 23 日更新以回应评论：评论是正确的，这可能会更有效。在我发布的时候，没有提供模块化的解决方案，所以我认真地提供了一个，如果不是最佳的。从那以后，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$
;
    • 23

  3. 这个问题是在PostgreSQL 邮件列表中提出的，我想出了这种通过 JSON 字段提取运算符将 JSON 文本转换为 PostgreSQL 文本类型的骇人听闻的方法：

    CREATE FUNCTION json_text(json) RETURNS text IMMUTABLE LANGUAGE sql
AS $$ SELECT ('['||$1||']')::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 $1->>i FROM generate_series(0, json_array_length($1)-1) AS i $$;

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

  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[];
    • 8

  5. 这几个函数取自这个问题的答案，是我正在使用的，它们运行良好

    CREATE OR REPLACE FUNCTION json_array_casttext(json) RETURNS text[] AS $f$
    SELECT array_agg(x) || ARRAY[]::text[] FROM json_array_elements_text($1) 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($1) 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($1) 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($1) t(x);
$f$ LANGUAGE sql IMMUTABLE;

    在他们每个人中，通过与一个空数组连接，他们处理了一个让我绞尽脑汁的案例，如果你尝试从json/jsonb没有它的情况下投射一个空数组，你将不会得到任何返回，而不是{}如您所料，空数组 ( )。我确信对它们进行了一些优化，但为了简单地解释这个概念，它们保持原样。

    • 1

  6. 我喜欢提到的翻译解决方案，所以这里是一个不干扰文本内容的致敬：

    SELECT ('{' || RIGHT(LEFT( '[1]'::json::text ,-1),-1) || '}')::INTEGER[]
    • 0

  7. 接受的答案的解决方案：

    CREATE OR REPLACE FUNCTION jsonb_array_to_text_array(_js jsonb)
  RETURNS text[]
  LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS
'SELECT ARRAY(SELECT jsonb_array_elements_text(_js))';

    甜美简洁，但对我有一个警告。当调用为：

    SELECT jsonb_array_to_text_array(NULL);

    根据我的用例的需要，它返回 NULL。但是，当传递一个 JSON 属性的 null 值时，例如：

    SELECT jsonb_array_to_text_array('{"my_value_is_null": null}'::JSONB -> 'my_value_is_null');

    它会抛出一个错误，指出“无法从标量中提取元素”。

    也许我错过了理解 NULL 值处理的重要部分，但我已经在自定义函数内部跟踪了这个问题，其中 null 值属性作为值“null”的字符串传入。所以我的修改版本：

    CREATE OR REPLACE FUNCTION jsonb_array_to_text_array(_js jsonb)
  RETURNS text[]
  LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS
$$SELECT CASE WHEN _js = 'null' THEN NULL ELSE ARRAY(SELECT jsonb_array_elements_text(_js)) END$$;

    在这种情况下也通过返回 NULL 来处理它，就好像传递了一个常规的 SQL NULL 值一样。

    • 0

相关问题