How to query Postgres JSONB arrays with = operator

Question

I'm looking for a way to query PostgreSQL JSONB array field with kind of = clause.

Let's assume I have a table

CREATE TABLE events(
   id integer,
   tags jsonb,
   PRIMARY KEY(id)
);

tags having values like ['Google', 'Hello World', 'Ruby']

I have gone through Stackover Flow and done similar things.

And the SQL formed is like

SELECT "events".* FROM "events" WHERE ("events"."tags" @> '{google}')  ORDER BY "business_events"."id" desc;

By running this, I'm getting this error =>

ERROR:  invalid input syntax for type json
LINE 1: ...siness_events" WHERE ("business_events"."tags" @> '{google}'...
                                                             ^
DETAIL:  Token "google" is invalid.
CONTEXT:  JSON data, line 1: {google...

Any Idea ?

klin · Accepted Answer · 2019-03-05T18:49:37.870

The right operand of the operator @> should be a valid json:

WITH events(id, tags) AS (
VALUES
    (1, '["Google", "Hello World", "Ruby"]'::jsonb)
)

SELECT events.* 
FROM events 
WHERE tags @> '["Google"]'

 id |               tags                
----+-----------------------------------
  1 | ["Google", "Hello World", "Ruby"]
(1 row)

Note that keys and text values of json objects are enclosed in double quotes.

The operator takes arguments as they are and there is no way to make it work case insensitive. You can use the function jsonb_array_elements_text() to accomplish this:

SELECT events.*
FROM events 
CROSS JOIN jsonb_array_elements_text(tags)
WHERE lower(value) = 'google';

The second solution is much more expensive, notes in the cited answer apply here as well.

How to make it case insensitive ? Any Idea ? – Jyothu Mar 05 '19 at 15:35 — Jyothu, Mar 05 '19 at 15:35

How to query Postgres JSONB arrays with = operator

1 Answers1