Use Values from JSONB Array inside a WHERE IN Clause

Question

I have a JSONB object in PostgreSQL:

'{"cars": ["bmw", "mercedes", "pinto"], "user_name": "ed"}'

I am trying to use values from the "cars" array inside it in the WHERE clause of a SELECT:

SELECT car_id FROM cars WHERE car_type IN ('bmw', 'mercedes', 'pinto');

This will correctly return the values 1, 2, and 3 - see table setup at the bottom of this post.

Currently, in my function I do this:

(1) Extract the "cars" array into a variable `v_car_results`.
(2) Use that variable in the `WHERE` clause.

Pseudo code:

    DECLARE v_car_results TEXT
    BEGIN
        v_car_results = '{"cars": ["bmw", "mercedes", "pinto"], "user_name": "ed"}'::json#>>'{cars}';
            -- this returns 'bmw', 'mercedes', 'pinto'
        SELECT car_id FROM cars WHERE car_type IN ( v_car_results );
    END

However, the SELECT statement is not returning any rows. I know it's reading those 3 car types as a single type. (If I only include one car_type in the "cars" element, the query works fine.)

How would I treat these values as an array inside the WHERE clause?

I've tried a few other things:

The ANY clause.
Various attempts at casting.

These queries:

SELECT car_id FROM cars
WHERE car_type IN (json_array_elements_text('["bmw", "mercedes", "pinto"]'));

...
WHERE car_type IN ('{"cars": ["bmw", "mercedes", "pinto"], "user_name": "ed"}':json->>'cars');

It feels like it's something simple I'm missing. But I've fallen down the rabbit hole on this one. (Maybe I shouldn't even be using the ::json#>> operator?)

TABLE SETUP

CREATE TABLE cars (
   car_id SMALLINT
 , car_type VARCHAR(255)
);

INSERT INTO cars (car_id, car_type)
VALUES
  (1, 'bmw')
, (2, 'mercedes')
, (3, 'pinto')
, (4, 'corolla');

SELECT car_id FROM cars
WHERE car_type IN ('bmw', 'mercedes', 'pinto'); -- Returns Values : 1, 2, 3

Erwin Brandstetter · Accepted Answer · 2022-06-19 21:11:27Z

11

^{Assuming at least the current Postgres 9.5.}

Use the set-returning function jsonb_array_elements_text() (as table function!) and join to the result:

SELECT c.car_id
FROM   jsonb_array_elements_text('{"cars": ["bmw", "mercedes", "pinto"]
                                 , "user_name": "ed"}'::jsonb->'cars') t(car_type)
JOIN   cars c USING (car_type);

Extract the JSON array from the object with jsonb->'cars' and pass the resulting JSON array (still data type jsonb) to the function. (The operator #> would do the job as well.)

Aside: ::json#>> isn't just an operator. It's a cast to json (::json), followed by the operator #>>. You don't need either.

The resulting type text conveniently matches your column type varchar(255), so we don't need type-casting. And assign the column name car_type to allow for the syntax shorthand with USING in the join condition.

This form is shorter, more elegant and typically a bit faster than alternatives with IN () or = ANY() - which would work too. Your attempts were pretty close, but you need a variant with a subquery. This would work:

SELECT car_id FROM cars
WHERE  car_type IN (SELECT json_array_elements_text('["bmw", "mercedes", "pinto"]'));

Or, cleaner:

SELECT car_id FROM cars
WHERE  car_type IN (SELECT * FROM json_array_elements_text('["bmw", "mercedes", "pinto"]'));

Detailed explanation:

How to use ANY instead of IN in a WHERE clause?

How to turn JSON array into Postgres array?

edited Jun 19, 2022 at 21:11

answered Jul 4, 2016 at 0:36

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

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

3 Comments

bjones1831 Over a year ago

Works like a charm! Thanks so much! That was absolutely killing my day! :-)

bjones1831 Over a year ago

You now what? I didn't see your updated solution. That definitely looks cleaner. I'll go with that one!

brg Over a year ago

Erwin you seem to be a helpful expert fixing postgresql queries in most of the questions i have seen. Do you know what the problem is with the sql query in this question: stackoverflow.com/questions/38679190/…

Collectives™ on Stack Overflow

Use Values from JSONB Array inside a WHERE IN Clause

TABLE SETUP

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

TABLE SETUP

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related