如何将JSON数组转换为文本数组？

Question

这个解决方法不起作用。

CREATE FUNCTION json_array_castext(json) RETURNS text[] AS $f$
  SELECT array_agg(x::text) FROM json_array_elements($1) t(x);
$f$ LANGUAGE sql IMMUTABLE;

-- Problem:
SELECT 'hello'='hello';  -- true...
SELECT  (json_array_castext('["hello","world"]'))[1] = 'hello'; -- false!

那么，如何获取真正的文本数组？

PS：使用被认为是“一等公民”的JSONb时，同样存在这个问题。

编辑：在@OtoShavadze提供的好答案之后（评论解决！），给PostgreSQL开发人员的一个“清单”：为什么x :: text不是强制转换？ （使用pg 9.5.6），为什么它不会产生警告或错误？

- Peter Krauss

---

请尝试使用 json_array_elements_text 替代 json_array_elements。 - Oto Shavadze

是的！感谢@OtoShavadze！请将其发布为答案，以便我正式接受（！）。我只是编辑一下，以向PostgreSQL开发人员表明；-) - Peter Krauss

Answer 1

尝试使用json_array_elements_text替代json_array_elements，无需显式地将文本进行转换(x::text)，因此您可以使用以下代码：

CREATE or replace FUNCTION json_array_castext(json) RETURNS text[] AS $f$
    SELECT array_agg(x) FROM json_array_elements_text($1) t(x);
$f$ LANGUAGE sql IMMUTABLE;

对于你的补充问题：

为什么"x::text"不是一种转换方式？

"x::text"是一种类型转换方式，因此它不会产生任何错误，但是当像这样将json字符串转换为文本时：::text，PostgreSQL会向值添加引号。

仅供测试目的，让我们将您的函数更改回原始状态（与您的问题中相同），并尝试：

SELECT  
(json_array_castext('["hello","world"]'))[1] = 'hello',
(json_array_castext('["hello","world"]'))[1],
'hello'

正如您所见，(json_array_castext('["hello","world"]'))[1] 的结果是 "hello" 而不是 hello。这就是为什么当比较这些值时，您得到了 false。

- Oto Shavadze

---

谢谢Oto！这不是批评，请忽略，只是对我的宣言的补充 :-) 在SQL中，将char(N)转换为期望的无引号text；在嵌入式语言或驱动程序中，将SQL-text转换为期望的无引号string，将string转换为期望的无引号SQL-text... 这是普遍预期的行为... - Peter Krauss

对于读者来说，这是最好的答案（教学和性能），并且一直是被接受的答案，直到2020年。新的被接受的是相同的解决方案，只是增加了一些针对null的扩展，因此它是您的“库解决方案”中最好的选择。 - Peter Krauss

Answer 2

针对PostgreSQL的这种不好看的行为，有一个不好看的转换解决方法，那就是使用运算符#>>'{}'：

CREATE or replace FUNCTION json_array_castext(json) RETURNS text[] AS $f$
    SELECT array_agg(x#>>'{}') FROM json_array_elements($1) t(x);
$f$ LANGUAGE sql IMMUTABLE;

SELECT  (json_array_castext('["hello","world"]'))[1] = 'hello'; -- true!

---

(编辑) 2020年，pg v12性能检查

我们预计专用函数json_array_elements_text()比用户定义的转换更好...但是，它有多好？两倍？20倍...还是只有几个百分点？
有时候我们不能使用它，所以会有一些性能损失吗？

测试准备：

CREATE TABLE j_array_test AS -- JSON
  SELECT   array_to_json(array[x,10000,2222222,33333333,99999,y]) AS j
  FROM generate_series(1, 1900) t1(x), generate_series(1, 1900) t2(y);

CREATE TABLE jb_array_test AS --JSONb
  SELECT   to_jsonb(array[x,10000,2222222,33333333,99999,y]) AS j
  FROM generate_series(1, 1900) t1(x), generate_series(1, 1900) t2(y);

CREATE FUNCTION ...

函数名称：

• j_op_cast(json) 使用 array_agg(x#>>'{}') FROM json_array_elements($1)
• jb_op_cast(jsonb) 使用 array_agg(x#>>'{}') FROM jsonb_array_elements($1)
• j_func_cast(json) 使用 array_agg(x) FROM json_array_elements_text($1)
• jb_func_cast(jsonb) 使用 array_agg(x) FROM jsonb_array_elements_text($1)

结果： 所有结果几乎相同，在数十亿（~3610000）次函数调用后才能感知到差异。对于数千次调用，它们的性能是相等的！

EXPLAIN ANALYZE select j_op_cast(j) from j_array_test; -- ~35000
EXPLAIN ANALYZE select j_func_cast(j) from j_array_test;  -- ~28000
-- Conclusion: about average time json_array_elements_text is ~22%  faster.
-- calculated as 200*(35000.-28000)/(28000+35000)

EXPLAIN ANALYZE select jb_op_cast(j) from jb_array_test; -- ~45000
EXPLAIN ANALYZE select jb_func_cast(j) from jb_array_test;  -- ~37000
-- Conclusion: about average time json_array_elements_text is ~20%  faster.
-- calculated as 200*(45000.-37000)/(45000+37000)

对于JSON和JSONb，它们的性能差异大约在20%左右，因此通常情况下（例如报告或微服务输出），可以忽略不计。

正如预期的那样，将JSON转换为文本比将JSONB转换为文本更快，因为JSON内部是文本，而JSONB不是。

---

PS：在Ubuntu 20 LTS虚拟机上使用PostgreSQL 12.4。

- Peter Krauss

Answer 3

CREATE or replace FUNCTION json_to_array(json) RETURNS text[] AS $f$
  SELECT coalesce(array_agg(x), 
    CASE WHEN $1 is null THEN null ELSE ARRAY[]::text[] END)
  FROM json_array_elements_text($1) t(x);
$f$ LANGUAGE sql IMMUTABLE;

测试用例：

• select json_to_array('["abc"]') => 一个元素的数组
• select json_to_array('[]') => 空数组
• select json_to_array(null) => null

- Tom Yeh

Answer 4

Postgres 9.6或更高版本的最佳转换函数如下：

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

ARRAY构造函数比array_agg()更经济实惠。参见：

• 为什么array_agg()比非聚合的ARRAY()构造函数慢？

将其标记为PARALLEL SAFE也很重要。

请参见dba.SE上此早期答案中的逐步说明：

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

- Erwin Brandstetter

Answer 5

Oto的答案解救了我，但它确实有一个边界案例让我头疼。由于强制转换的丢失性质，在你有一个空的json数组的情况下，它可以完美地工作，但在这种情况下，你会期望返回一个空数组，但实际上它没有返回任何东西。作为一种解决方法，如果你只是将返回值与一个空数组连接起来，在实际有返回值的情况下不会产生影响，但在你有一个空数组的情况下会做正确的事情。这里是更新后的SQL函数（适用于json和jsonb），它们实现了这种解决方法。

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;

Postgres在将文档数据库集成到成熟的关系型数据库中存在一些类似这样的特殊情况，但它处理大多数问题做得很好。

- Joel B

---

嗨@JoelB，对于空情况，Oto的解决方案得到了很好的改进。您的解决方案非常优雅，也许是最好的...但也许（需要测试性能！）更快的解决方案是SELECT CASE WHEN $1='[]'::jsonb THEN array[]::text[] ELSE (SELECT array_agg(x) FROM etc) END。对于json也是一样，只需使用$1::text='{}'::text。 - Peter Krauss

哦，是的，这是一个完全的hack，我相信你的建议会更高效。我的主要关注点不是_悄悄地_丢失数据，而这个方法可以避免这种情况。 - Joel B

我认为coalesce(array_agg(x), array[]::text[])比连接更“清晰”（至少对我来说）。 - user330315

@a_horse_with_no_name 你说得完全正确。我发现Postgres针对我能想到的每种情况都有内置功能。请随意使用您的建议编辑/更新帖子。 - Joel B

Answer 6

table

CREATE TABLE IF NOT EXISTS public.j
(
    id integer,
    data jsonb
)

我找到的最简单的解决方案

SELECT j.id, array_agg(d.elem::int2) AS ar_values
FROM j
CROSS JOIN LATERAL jsonb_array_elements(j.data) k
CROSS JOIN LATERAL jsonb_array_elements_text(k->'ar') AS d(elem)
GROUP BY j.id;

可以简化。

SELECT j.id, array_agg(d.elem::int2) AS ar_values
FROM j
, LATERAL jsonb_array_elements(j.data) k
, LATERAL jsonb_array_elements_text(k->'ar') AS d(elem)
GROUP BY j.id;

- Rami Jamleh

Answer 7

在我这种情况下，将结果反映出3个状态是有帮助的，即null，空文本数组和非空文本数组，具体取决于输入。希望这对某些人有用。

CREATE OR REPLACE FUNCTION json_array_text_array(JSON)
  RETURNS TEXT [] AS $$
DECLARE
  result TEXT [];
BEGIN
  IF $1 ISNULL
  THEN
    result := NULL;
  ELSEIF json_array_length($1) = 0
    THEN
      result := ARRAY [] :: TEXT [];
  ELSE
    SELECT array_agg(x) FROM json_array_elements_text($1) t(x) INTO result;
  END IF;
  RETURN result;
END;
$$
LANGUAGE plpgsql
IMMUTABLE
STRICT;

- Pawel Zieminski

---

嗨，Pawel，一般来说，我们更喜欢使用SQL语言而不是PLpgSQL，因为SQL可以更快，主要是在不可变函数的内联优化方面，也可以参考这个讨论。我们还应该尽量避免使用STRICT，因为PostgreSQL优化计划器中存在的一个错误。 - Peter Krauss