CODEWITHSUNDEEP
postgresqljsonbpostgresql-9.5
Programatt
Programatt

Reputation: 836

find all rows that for a postgres jsonb column that have a specfic array value

(using Postgres 9.5)

Say you have a Postgres table with the following schema:


                                            Table "public.tableA"
        Column     |           Type           | Collation | Nullable |                   Default
    ---------------+--------------------------+-----------+----------+----------------------------------------------
     id            | bigint                   |           | not null | nextval('truelayer_tokens_id_seq'::regclass)
     user_id       | text                     |           | not null |
     created_at    | timestamp with time zone |           |          | now()
     updated_at    | timestamp with time zone |           |          |
     provider_id   | text                     |           | not null | ''::text
     scopes        | jsonb                    |           | not null | '{}'::jsonb

and inside the scopes column is the a subset of the following data for various user_ids. ["A", "B", "C", "D", "E"]

I want to run a query that returns all the rows for a specific user_id where the array in "scopes" contains the value "A"

I am struggling to even get started with this so all advice appreciated.

Upvotes: 0

Views: 102

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246188

Use the jsonb containment operator @>:

SELECT id, scopes
FROM "tableA"
WHERE scopes @> '["A"]';

Upvotes: 1

Related Questions