Postgres: check if array field contains a jsonb value with a specific property?

Question

Here's an example setup:

CREATE TABLE users (
    id text NOT NULL,
    pets jsonb[] NOT NULL
);

INSERT INTO users (id, pets) values ('random-id', array[
    '{"name": "Spot", "toys": [{"color": "red"}, {"color": "blue"}]}',
    '{"name": "Zeus", "toys": [{"color": "purple"}, {"color": "red"}]}'
]::jsonb[]);

Example query: How do I find ids of all users with at least one pet with at least one red toy?

Why do you use jsonb[] instead of jsonb simply? Json does support arrays, an array of jsons is an unnecessary complication. With this approach you probably have to unnest the table first. — freakish
– freakish, Commented Feb 1, 2022 at 19:35
jsonb[] almost never makes sense. It's better to use jsonb and store a JSON array in it (not an array of jsonb) — user330315
– user330315, Commented Feb 1, 2022 at 19:39
Sorry, I didn't choose the schema of the DB when it was created! Is there a way to do it with the current schema? edit: I'm looking into the unnest function... that seems useful — Eric Chen
– Eric Chen, Commented Feb 1, 2022 at 19:43

freakish · Accepted Answer · 2022-02-01 19:47:56Z

1

The schema is... problematic. You should really use jsonb instead of jsonb[] since json supports arrays out of the box.

Anyway, if the schema is as it is, then you can utilize something like this:

select distinct tmp2.id
from (
    select
        tmp1.id,
        jsonb_array_elements(tmp1.pets->'toys') as toys
    from (
        select
            id,
            unnest(pets) as pets
        from users
    ) tmp1
) tmp2
where jsonb_extract_path_text(tmp2.toys, 'color') = 'red'

(it can be probably written in a more readable way by utilizing with instead of nesting selects)

Explanation:

unnest will turn an internal array into separate rows.

jsonb_array_elements does the same thing, except it operates on jsonb arrays (we have to unwrap the internal toys arrays as well).

jsonb_extract_path_text retrieves the internal text stored under key.

Note that the query assumes the specific format you have. I didn't test the query against other json variants.

answered Feb 1, 2022 at 19:47

freakish

57k12 gold badges141 silver badges181 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Eric Chen Over a year ago

Thanks for the answer! This works. Following up, is there a way to modify this query to change all the red colors to blue?

freakish Over a year ago

@EricChen sure, you just need to change 'red' string at the end to 'blue'.

Eric Chen Over a year ago

Oh, I meant instead of just SELECT-ing the user ids, we UPDATE the table by changing all 'red' values to 'blue'.

Giorgi Over a year ago

@freakish How would I do it if the column was just jsonb?

Collectives™ on Stack Overflow

Postgres: check if array field contains a jsonb value with a specific property?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related