我有一个具有以下架构的表(postgresql 14):

message   sentiment     classification
any text  positive    mobile, communication

message只是字符串、短语.

我想用这些列创建一个json字段,如下所示:

{"msg":"any text", "sentiment":"positive","classification":["mobile,"communication"]}

此外,如果可能,是否有办法这样考虑分类:

{"msg":"any text", "sentiment":"positive","classification 1":"mobile","classification 2" communication"}

推荐答案

问题的第一部分很简单-Postgres提供了拆分字符串和转换为json的函数:

with t(message,   sentiment,     classification) as (values
('any text','positive','mobile, communication')
)
select row_to_json(x.*)
from (
  select t.message
       , t.sentiment
       , array_to_json(string_to_array(t.classification, ', ')) as classification
  from t
) x

第二部分更难——您希望json具有数量可变的属性,混合分组和非分组数据.我建议展开所有属性,然后将其重新组合(注意,如果实际表有id,则实际上不需要numbered CTE-我只需要一些列来分组):

with t(message,   sentiment,     classification) as (values
('any text','positive','mobile, communication')
)
, numbered (id, message,   sentiment,     classification) as (
  select row_number() over (order by null)
       , t.*
  from t
)
, extracted (id,message,sentiment,classification,index) as (
  select n.id
       , n.message
       , n.sentiment
       , l.c
       , l.i
  from numbered n
  join lateral unnest(string_to_array(n.classification, ', ')) with ordinality l(c,i) on true
), unioned (id, attribute, value) as (
  select id, concat('classification ', index::text), classification
  from extracted
  union all
  select id, 'message', message
  from numbered
  union all
  select id, 'sentiment', sentiment
  from numbered
)
select json_object_agg(attribute, value)
from unioned
group by id;

DB fiddle

Sql相关问答推荐

Postgres JSONB对象筛选

如何根据特定的内部json元素值过滤postgres查询结果?

如何查询jsonb列是一个对象数组?

使用列表作为参数进行 Select ,如果为空,则在PostgreSQL中不使用参数进行 Select

SQL:查询作为子查询或CTE写入的最大和数失败

用于SQL协助的XQUERY()

使用特定的Order By子句随机化SQL输出

将时间范围划分为全天和前后剩余小时

存储过程太慢

将最近的结束日期与开始日期相匹配

当我返回 sql 列时,有没有办法只反转数字? ( hebrew )

两个具有 NULL 值的表达式结果之间的差异

Oracle 21c 中的递归查询回顾过go 3 周

Oracle函数中无法动态迭代创建的SYS_REFCURSOR

Teradata 多个进程的最大进程结束时间捕获

String_Split 多列

将有效数字作为 varchar 返回的 SQL 函数

如何将多行的查询结果合并为一行

根据开始/结束标记将 GROUP_ID 分配给行

每组跨行曲折?