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

发布于01月28日

我有一个postgres数据库，其中包含一个具有jsonb列tsent的表，该表包含对另一列中的文本的目标情感分析的结果.数据的 struct 如下:

CREATE TABLE tbl(id int PRIMARY KEY, tsent jsonb);

INSERT INTO tbl VALUES
  (1, '[
    {
        "class": 0,
        "entity": "John Smith",
        "sent_no": 0,
        "class_label": "negative",
        "entity_type": "PER",
        "sentiment_prob": 0.95
    },
    {
        "class": 1,
        "entity": "University of Illinois",
        "sent_no": 0,
        "class_label": "neutral",
        "entity_type": "ORG",
        "sentiment_prob": 0.95
    }
   ]');

它是一个对象数组，因为一个给定的文本可以有多个实体.我想知道如何编写一个查询，检索所有以John Smith为实体的记录，以及那些以John Smith为实体且class_label为负数的记录.

我仍在学习jsonb，知道如何查询是否存在键，但不知道如何查询数组中的任何元素中是否存在键.

推荐答案

有几种方法可以做到这一点，但这可能是最通用和最清楚的(但不一定是最有效的)-

将带有JSON对象的表格展平为"常规"表格
使用该表执行所需的任何查询

把桌子压平(lateral join在这里是不重要的):

 select id, content, t.*
 from tbl,
  lateral jsonb_to_recordset(tsent) as t(
      class integer,
      entity text,
      sent_no integer,
      class_label text,
      entity_type text, 
      sentiment_prob numeric
  );

id	content	class	entity	sent_no	class_label	entity_type	sentiment_prob
1		0	John Smith	0	negative	PER	0.95
1		1	University of Illinois	0	neutral	ORG	0.95

使用上面的"平面"表查询(tCTE):

with t as 
(
 select id, content, t.*
 from tbl,
    lateral jsonb_to_recordset(tsent) as t(
      class integer,
      entity text,
      sent_no integer,
      class_label text,
      entity_type text, 
      sentiment_prob numeric
  )
)
select content, <other expression(s) here>
from t
where <your logic here>;