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

Question

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

klin · Accepted Answer · 2019-11-20 00:26:29Z

5

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")')

answered Nov 20, 2019 at 0:26

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dennis Over a year ago

I've made an update at the bottom. Can you inspect it please?

Dennis Over a year ago

No need to inspect. I just added secondary path and it worked.

GMB · Accepted Answer · 2019-11-20 00:21:27Z

2

You can use the ? operator:

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

Note: I am unsure that you do need jsonb_path_query() here. This is a set-returning function that does not really seems to make sense in your context. Your query could probably be phrased:

SELECT "data" ->> 'badges' AS "badges"
FROM "users_tbl"
WHERE "data" -> 'badges' ? 'gold';

Demo on DB Fiddle:

| badges             |
| :----------------- |
| ["gold", "silver"] |
| ["gold"]           |

answered Nov 20, 2019 at 0:21

GMB

224k25 gold badges103 silver badges151 bronze badges

3 Comments

Dennis Over a year ago

I've made an update at the bottom. Can you inspect it please?

GMB Over a year ago

@Pratha: that would be:

SELECT "data" -> 'properties' ->> 'badges' AS "badges" FROM "users_tbl" WHERE "data" -> 'properties' -> 'badges' ? 'gold';

Dennis Over a year ago

Thanks. I just can't decide if I should go with your or jsonb_path_exists from @klin's answer. This column can be NULL, can be empty or may not have properties/badges at all. Does this matters on yours?

Bergi · Accepted Answer · 2019-11-20 00:36:28Z

1

How can I add condition against badges?

You can't really. As the error message says, it's not a column of the table - that's what you'd need to refer to in the WHERE clause. You cannot take the selection alias to refer to the result value of the expression. You could repeat it

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

or use a LATERAL subquery:

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

edited Nov 20, 2019 at 0:36

answered Nov 20, 2019 at 0:30

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

2 Comments

Dennis Over a year ago

I got set-returning functions are not allowed in WHERE on first query of yours.

Bergi Over a year ago

@Pratha Oh, I forgot that jsonb_path_query is a set-returning function - you only expect one array value back. I've changed it to use jsonb_path_query_first, although definitely I would suggest using the simple -> operator as in the other answers, a jsonpath is overkill here. (I just wanted the code to resemble your original more)

Collectives™ on Stack Overflow

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

Expected result:

Actual Result:

Update:

3 Answers 3

2 Comments

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Expected result:

Actual Result:

Update:

3 Answers 3

2 Comments

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related