Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tbl(id int, 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
    }
   ]')

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

  , (3, '[
    {
        "class": 0,
        "entity": "Jane 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
    }
   ]')
RETURNING *;

CREATE TABLE




id
tsent


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}]

2
[{"class": 0, "entity": "John Smith", "sent_no": 0, "class_label": "neutral", "entity_type": "PER", "sentiment_prob": 0.95}, {"class": 1, "entity": "University of Illinois", "sent_no": 0, "class_label": "negative", "entity_type": "ORG", "sentiment_prob": 0.95}]

3
[{"class": 0, "entity": "Jane 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}]



INSERT 0 3


-- iondex?
CREATE INDEX tbl_tsent_gin_idx ON tbl USING gin (tsent jsonb_path_ops);

CREATE INDEX


SELECT *
FROM tbl
WHERE tsent @? '$[*] ? (@.entity == "John Smith" && @.class_label == "negative")';


id
tsent


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}]



SELECT 1

id	tsent
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}]
2	[{"class": 0, "entity": "John Smith", "sent_no": 0, "class_label": "neutral", "entity_type": "PER", "sentiment_prob": 0.95}, {"class": 1, "entity": "University of Illinois", "sent_no": 0, "class_label": "negative", "entity_type": "ORG", "sentiment_prob": 0.95}]
3	[{"class": 0, "entity": "Jane 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}]