sql - 如何使用数据将数组更改为 jsonb？

Question

我有这样的数据数组列：

{foo,bar}
{foo}
{foo,foobar}
...

我想将其转换为 jsonb：

[{"my_key": "foo", "state": true}, {"my_key": "bar", "state": true}]
[{"my_key": "foo", "state": true}]
[{"my_key": "foo", "state": true}, {"my_key": "foobar", "state": false}]
...

“状态”在哪里

case when type = ANY('{"foo","bar"}'::text[]) then true
else false

我怎样才能做到这一点？谢谢

标签： sqljsonpostgresql

Answer 1

您可以取消嵌套值并使用横向连接将它们聚合回来：

with t (data) as (
values 
  (array['foo','bar']),
  (array['foo']),
  (array['foo', 'foobar'])
)
select x.*
from t
  cross join lateral (
      select jsonb_agg(jsonb_build_object(
                          'my_key', x.type, 
                          'state', x.type = any(array['foo','bar']))
                       ) as val
      from unnest(t.data) as x(type)
  ) x;

返回：

val                                                                     
------------------------------------------------------------------------
[{"state": true, "my_key": "foo"}, {"state": true, "my_key": "bar"}]    
[{"state": true, "my_key": "foo"}]                                      
[{"state": true, "my_key": "foo"}, {"state": false, "my_key": "foobar"}]

在线示例：https ://rextester.com/MWUER75686