Skip to content
Advertisement

How can I add condition based on select value (JSONB) on PostgreSQL 12?

I want to filter rows that have gold badges. I am using PG12 and I use new path feature of it.

To clarify, I have a users_tbl table like this:

CREATE TABLE users_tbl (
   ID serial NOT NULL PRIMARY KEY,
   data jsonb NOT NULL
); 

And lets insert values:

INSERT INTO users_tbl (ID, data) values (1, '{"badges": ["gold", "silver"], "another_field": 1}');
INSERT INTO users_tbl (ID, data) values (2, '{"badges": ["silver"], "another_field": 3}');
INSERT INTO users_tbl (ID, data) values (3, '{"badges": ["gold"], "another_field": 4}');

Now when I query like this:

SELECT
  ID, jsonb_path_query("data", '$.badges') AS "badges"
FROM "users_tbl";

I get following result as expected:

+----+--------------------+
+ ID |       badges       +
+----+--------------------+
+ 1  | ["gold", "silver"] +
+----+--------------------+
+ 2  | ["silver"]         +
+----+--------------------+
+ 3  | ["gold"]           +
+----+--------------------+

Now list only have badge matches gold

SELECT
  jsonb_path_query("data", '$.badges') AS "badges"
FROM "users_tbl"
WHERE "badges" @> 'gold';

Expected result:

+----+--------------------+
+ ID |       badges       +
+----+--------------------+
+ 1  | ["gold", "silver"] +
+----+--------------------+
+ 3  | ["gold"]           +
+----+--------------------+

Actual Result:

column "badges" does not exist 

How can I add condition against badges? Or am I doing something not correct? How can I get expected result in my case?

Note: PostgreSQL 12.

Update:

Actually in this example I’ve used a simple jsonb object. In reality its like this:

{
  "properties": {
    "badges": ["gold", "silver"]
  }
}

So, badges are in properties

Advertisement

Answer

You can use the containment operator:

select
    jsonb_path_query(data, '$.badges') as badges
from users_tbl
where data->'badges' @> '"gold"';

or jsonb_path_exists():

select
    jsonb_path_query(data, '$.badges') as badges
from users_tbl
where jsonb_path_exists(data, '$.badges ? (@[*] == "gold")')

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement