Stack Exchange
How to query jsonb column that is an array of objects?
I have a Postgres db containing a table with a jsonb column tsent that contains the results of targeted sentiment analysis of text in another column. The data is structured like so:
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
    }
   ]');

It is an array of objects because a given text could have more than one entity. I am wondering how to write a query that would retrieve all records that have John Smith as an entity, and also those that have John Smith as an entity and class_label negative for him.
I am still learning jsonb and know how to query for a key being present, but not how to query for a key being present in any element in an array.

There are several ways to do that but this is probably the most generic and clear (yet not necessarily the most efficient) -

Flatten the table with a JSON object into a "regular" table
do whatever querying you need using this table

Flatten the table (a lateral join is insrtumental here):
 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




Query using the above "flat" table (t CTE):
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>;


In that second case, does t just contain the flattened contents of tsent or tbl columns plus t columns? Because what I ultimately want to do is get the text from tbl.content where the entity conditions are met

No it does not. If you need tbl.content then add it to the CTE select list so that t will contain it. Answer edited.

To have all tbl columns plus the flattened contents of tsent in the CTE just select * from tbl, lateral ... in the CTE's body.

And of course you can implement your select list and "where" logic directly in the first query w/o a CTE. The second one is there only because it is step-by-step illustrative.

OK tried the first, appending where t.entity = 'John Smith' and it returned zero rows.

You can use a Json Path query.
SELECT *
FROM tbl
WHERE tsent @? '$[*] ? (@.entity == "John Smith" && @.class_label == "negative")';

db<>fiddle
@? is an "exists" predicate on the whole JSON object, @@ retrives the actual values at that path. For more info see the docs.

When I try that, the query runs but returns zero records for multiple entity and class_label combos that I know exist.

You would have to give a proper example. Maybe your JSON structure isn't as simple as this?

A plain expression with the jsonb contains operator @> does it:
SELECT *
FROM   tbl
WHERE  tsent @> '[{"entity": "John Smith","class_label": "negative"}]';

fiddle
Can even use index support. See:

What's the proper index for querying structures in arrays in Postgres jsonb?

