Query JSONB without keys

0
votes

I have a table called test. Inside this table is a column called letters of jsonb. Inside this column is data like [], ["a"], ["b"], ["a", "b","c"] but there are no key values. How can I query all the rows that contains one of these like "b" (So, the last 2)?

What I have isn't currently working.

I'm trying:

SELECT *
    FROM db.test
    WHERE (letters->>'')::jsonb ?| array['b'];

Also tried

SELECT *
    FROM db.test
    WHERE letters::jsonb ?| array['b'];

SELECT *
        FROM db.test
        WHERE letters::jsonb ? 'b';
2
sqlpostgresqljsonb
"SELECT * FROM db.test WHERE letters::jsonb ?| array['b'];" This one works for me. What do you mean it doesn't work? What happens instead of working? (Although the cast to jsonb is pointless if that is already the type of the column.) - jjanes

2 Answers

1
votes

If you're on postgres 12, you can use jsonb_path_exists:

# create table test (letters jsonb);
# insert into test VALUES ('[]'), ('["a"]'), ('["a", "b", "c"]');
# select letters
  FROM test where jsonb_path_exists(letters, 'strict $[*] ? (@ == "b")');
     letters
-----------------
 ["a", "b", "c"]
(1 row)
1
votes

Use the containment operator:

select *
from test
where letters @> '"b"'

Db<>fidlle.