将列拆分为PostgresQL中的多列

Question

我有一张桌子，像：

 id     Name   f_data
  1     Raj   {"review":"perfect", "tech":{"scalability":"complete", "backbonetech":["satellite"], "lastmiletech":["Fiber","Wireless","DSL"] }}

我想将f_data列拆分为多个列。预期结果：

 id     Name    review   scalability   backbonetech   lastmiletech
  1     Raj     perfect    complete      satellite   Fiber,wireless,DSL

当我用托盘分割json列时，我无法移除括号。我的产出是：

 id  Name  review  scalability backbonetech   lastmiletech
  1  Raj   perfect complete   ["satellite"]   ["Fiber","wireless","DSL"]

我使用了这个代码：

SELECT id, Name,
   f_data->'review' ->>0 as review,
   f_data->'tech' ->> 'scalability' as scalability,
   f_data->'tech' ->> 'backbonetech' as backbonetech,
   f_data->'tech' ->> 'lastmiletech' as lastmileteck
from my_table;

Answer 1

一种可能的方法是使用json_array_elements_text函数将JSON中的数组转换为一组文本，然后使用string_agg函数将单个元素连接到一个字符串中。

对于lastmiletech来说，请求可能如下所示：

select
    string_agg(t.lastmiletech, ',') as lastmiletech
from 
(
    select 
       json_array_elements_text('{"scalability":"complete", "backbonetech":>["satellite"], "lastmiletech":["Fiber","Wireless","DSL"]}'::json->'lastmiletech') as lastmiletech
) t

可以修改子查询以包括所需的其他字段。

顾名思义，json_array_elements_text的第一个参数必须是json数组，所以在将数组传递给函数之前，不要将数组转换为文本。换句话说，f_data->tech->>lastmiletech将不被接受。