Postgresql - How to query jsonb column that is an array of objects?

Steve
January 28, 2024
171 views
0 votes
2 Answers

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.

Answers

- ErwinBrandstetter
- January 28, 2024 at 1:21 am
- 0 votes
0
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?
Login or Signup to reply.

- Yakir
- January 28, 2024 at 1:21 am
- 0 votes
0
To query a jsonb column in PostgreSQL that contains an array of objects, like in your case with the tsent column, you can utilize the jsonb_array_elements function along with the ->> operator for accessing object fields. Below are two queries tailored to your requirements:

Query 1: Retrieve Records with "John Smith" as an Entity

To find all records where "John Smith" is mentioned as an entity in any of the objects within the tsent array, you can use:
```
SELECT *
FROM your_table
WHERE EXISTS (
    SELECT 1
    FROM jsonb_array_elements(tsent) AS elem
    WHERE elem->>'entity' = 'John Smith'
);
```
This query filters rows based on the existence of at least one object in the tsent array where the entity key has the value "John Smith".

Query 2: Retrieve Records with "John Smith" as an Entity and a "negative" Class Label

To further refine the search to only include records where "John Smith" also has a "negative" class label, you can add an additional condition:
```
SELECT *
FROM your_table
WHERE EXISTS (
    SELECT 1
    FROM jsonb_array_elements(tsent) AS elem
    WHERE elem->>'entity' = 'John Smith'
    AND elem->>'class_label' = 'negative'
);
```
Explanation
- jsonb_array_elements(tsent): Expands the JSONB array tsent into a set of JSONB elements, allowing us to inspect each object in the array.
- elem->>'key': Fetches the string value of the specified key in a JSONB object, where elem represents an element in the array.
- EXISTS: Checks for the existence of rows meeting the criteria within the subquery, useful for situations where you’re interested in the presence of certain JSONB patterns.
These queries effectively leverage PostgreSQL’s JSONB functions to filter for specific conditions within an array of JSONB objects. Make sure to replace your_table with the actual name of your table.
Login or Signup to reply.