代码之家 › 专栏 › 技术社区 › ldeld

如果元素是PostgreSQL中的JSON字符串,则动态将其强制转换为JSON数组

postgresql json sql

ldeld · 技术社区 · 7 年前

基本上,我必须从格式糟糕的JSON对象中选择一个值。这个值可以是一个字符串或JSON数组,如果它是一个数组,我应该将它的元素聚合成一个逗号分隔的字符串。

例如,我的表可能如下所示:

  id  |                 field
------+--------------------------------------
  1   |            {"key": "string"} 
---------------------------------------------
  2   |  {"key": ["array", "of", "strings"]}

我需要这样的结果

"string", 
"array, of, strings"

我使用以下查询:

SELECT my_table.id,
       array_to_string(ARRAY(SELECT json_array_elements_text(field -> json_object_keys(field))), ', '),
FROM my_table

将JSON数组聚合为字符串。但是,当值是字符串时,我得到 ERROR: cannot call json_array_elements_text on a scalar . 所以现在下一步看起来像是将值强制转换为一个字符串形式的JSON数组(基本上用括号括起来)。

我该怎么做?我看过postgres文档,但没有看到要强制转换为不同JSON类型的函数。

我用的是PostgreSQL 10

1 回复 | 直到 7 年前

Vao Tsun 7 年前

with c(id,field) as (values(1,'{"key": "string"}'::json),(2, '{"key": ["array", "of", "strings"]}'))
, pg10 as (select id, case when json_typeof(field->'key') != 'array' then json_build_array(field->>'key') else field->'key' end field from c)
, m as (select id, json_array_elements_text(field) field from pg10)
select distinct id, string_agg(field,', ') over (partition by id) field from m;

正如您在评论中提到的,您需要额外的步骤来消除SRF case

https://www.db-fiddle.com/f/mr5rWGpC6xRoBvUKwR7RTN/0