Use Values from JSONB Array inside a WHERE IN Clause
<p>I have a <code>JSONB</code> object in PostgreSQL:</p>

<pre><code>'{"cars": ["bmw", "mercedes", "pinto"], "user_name": "ed"}'
</code></pre>

<p>I am trying to use <strong>values</strong> from the "cars" array inside it in the <code>WHERE</code> clause of a <code>SELECT</code>:</p>

<pre><code>SELECT car_id FROM cars WHERE car_type IN ('bmw', 'mercedes', 'pinto');
</code></pre>

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

<p>Currently, in my function I do this:</p>

<pre><code>(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#&gt;&gt;'{cars}';
            -- this returns 'bmw', 'mercedes', 'pinto'
        SELECT car_id FROM cars WHERE car_type IN ( v_car_results );
    END
</code></pre>

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

<p>How would I treat these values as an <em>array</em> inside the <code>WHERE</code> clause?</p>

<p>I've tried a few other things:</p>

<ol>
<li><p>The <code>ANY</code> clause.</p></li>
<li><p>Various attempts at <em>casting</em>.</p></li>
<li><p>These queries:</p>

<pre><code>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-&gt;&gt;'cars');
</code></pre></li>
</ol>

<p>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 <code>::json#&gt;&gt;</code> operator?)</p>

<h3>TABLE SETUP</h3>

<pre><code>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
</code></pre>