主页 / dba / 问题 / 241291
Accepted
daniel9x
Asked: 2019-06-25 09:26:17 +0800 CST

将 JSON 数组展平为逗号分隔列表

  • 772

我正在尝试构建一个SELECT语句，该语句将采用JSONB以下格式的列：

{
  "Cities": [
    {
      "Name": "Atlanta"
    },
    {
      "Name": "London"
    },
    {
      "Name": "New York"
    }
  ]
}

列结果集的输出需要采用以下格式：

Atlanta, London, New York

更新

@a_horse_with_no_name 下面的答案是正确的，但我的要求实际上比最初发布的要复杂一些。我实际上需要将此选择放入更大的（连接）查询中，如下所示：

select eo.citydata.cities.names <-- Flattened Comma delimited JSON Array
from orderline o 
join eventorders eo on eo.orderlineid = o.id
join events e on e.id = eo.eventid
where e.id = '123'

显然，需要修改提供的答案才能使其正常工作，我正在努力弄清楚如何去做。

postgresql json

1 个回答

  1. Best Answer

    取消嵌套数组，然后聚合回来：

    select string_agg(city, ',')
from (
  select x.val ->> 'Name' as city
  from the_table t
     cross join jsonb_array_elements(t.the_column -> 'Cities') as x(val)
) t;

    如果您有更大的查询，请在派生表中使用它：

    select string_agg(t2.city, ',')
from (
  select x.val ->> 'Name' as city
  from (
    select eo.citydata
    from orderline o 
      join eventorders eo on eo.orderlineid = o.id
      join events e on e.id = eo.eventid
    where e.id = 123
  ) t1
     cross join jsonb_array_elements(t1.citydata -> 'Cities') as x(val)
) t2;

    或者 - 如果您经常需要 - 您可以创建一个执行此操作的函数：

    create function get_element_list(p_value jsonb, p_keyname text)
  returns text
as 
$$ 
   select string_agg(x.val ->> p_keyname, ',')
   from jsonb_array_elements(p_value) as x(val);
$$
language sql;

    然后你可以像这样使用它：

    select get_element_list(eo.citydata -> 'Cities', 'Name')
from orderline o 
  join eventorders eo on eo.orderlineid = o.id
  join events e on e.id = eo.eventid
where e.id = 123;
    • 3

相关问题