PostgreSQL query filtering results by conditions based on jsonb

Question

I have a data in my DB similar to next rows:

id	code	custom
1	GOOG	[{"customBreakdown":{"id":15,"name":"By PO"},"customBreakdownGroup":{"id":72,"name":"team"}},{"customBreakdown":{"id":14,"name":"By SOW"},"customBreakdownGroup":{"id":72,"name":"poland"}}]
2	GOOG	[{"customBreakdown":{"id":15,"name":"By SOW"},"customBreakdownGroup":{"id":72,"name":"team"}},{"customBreakdown":{"id":17,"name":"By SOW/PO"},"customBreakdownGroup":{"id":72,"name":"poland"}}]
3	GOOG	[{"customBreakdown":{"id":15,"name":"By PO"},"customBreakdownGroup":{"id":72,"name":"team"}},{"customBreakdown":{"id":14,"name":"By SOW"},"customBreakdownGroup":{"id":73,"name":"Ungrouped"}}]
4	GOOG	[{"customBreakdown":{"id":15,"name":"By PO"},"customBreakdownGroup":{"id":72,"name":"team"}},{"customBreakdown":{"id":14,"name":"By SOW/PO"},"customBreakdownGroup":{"id":73,"name":"Ungrouped"}}]
5	GOOG	[]
12	AW	[{"customBreakdown":{"id":15,"name":"By PO"},"customBreakdownGroup":{"id":72,"name":"team"}},{"customBreakdown":{"id":17,"name":"By SOW/PO"},"customBreakdownGroup":{"id":72,"name":"poland"}}]
13	AW	[{"customBreakdown":{"id":15,"name":"By PO"},"customBreakdownGroup":{"id":72,"name":"team"}},{"customBreakdown":{"id":14,"name":"By SOW"},"customBreakdownGroup":{"id":73,"name":"Ungrouped"}}]
14	AW	[{"customBreakdown":{"id":15,"name":"By PO"},"customBreakdownGroup":{"id":72,"name":"team"}},{"customBreakdown":{"id":14,"name":"By SOW/PO"},"customBreakdownGroup":{"id":73,"name":"Ungrouped"}}]
15	AW	[]

making sql query I need as result all codes + customBreakdownName + customBreakdownGroupName where

custom[any obj]->'customBreakdown'->>'name' = 'By SOW' and custom[any obj in arr]->'customBreakdownGroup'->>'name' != 'Ungrouped'
if previous conditions doesn't met but code present, return the code and empty customBreakdownName and customBreakdownGroupName

the query to table above have to return next result:

code	customBreakdownName	customBreakdownGroupName
GOOG	By SOW	poland
GOOG	By SOW	team
AW	NULL	NULL

My query that I've implemented needs more complex condition to manage last row as result.

P.S.

Prepared PostgreSQL fiddle

Or if you prefer manage solution without fiddle you can use next data.

Postgresql table and data:

CREATE TABLE a (
    id int,
    code text,
    custom text
);

INSERT INTO a VALUES
(1,'GOOG','[{"customBreakdown":{"id":15,"name":"By PO"},"customBreakdownGroup":{"id":72,"name":"team"}},{"customBreakdown":{"id":14,"name":"By SOW"},"customBreakdownGroup":{"id":72,"name":"poland"}}]'),
(2,'GOOG','[{"customBreakdown":{"id":15,"name":"By SOW"},"customBreakdownGroup":{"id":72,"name":"team"}},{"customBreakdown":{"id":17,"name":"By SOW/PO"},"customBreakdownGroup":{"id":72,"name":"poland"}}]'),
(3,'GOOG','[{"customBreakdown":{"id":15,"name":"By PO"},"customBreakdownGroup":{"id":72,"name":"team"}},{"customBreakdown":{"id":14,"name":"By SOW"},"customBreakdownGroup":{"id":73,"name":"Ungrouped"}}]'),
(4,'GOOG','[{"customBreakdown":{"id":15,"name":"By PO"},"customBreakdownGroup":{"id":72,"name":"team"}},{"customBreakdown":{"id":14,"name":"By SOW/PO"},"customBreakdownGroup":{"id":73,"name":"Ungrouped"}}]')
 ,
(5,'GOOG','[]')--
--(6,''),
--(7,null)
,(12,'AW','[{"customBreakdown":{"id":15,"name":"By PO"},"customBreakdownGroup":{"id":72,"name":"team"}},{"customBreakdown":{"id":17,"name":"By SOW/PO"},"customBreakdownGroup":{"id":72,"name":"poland"}}]'),
(13,'AW','[{"customBreakdown":{"id":15,"name":"By PO"},"customBreakdownGroup":{"id":72,"name":"team"}},{"customBreakdown":{"id":14,"name":"By SOW"},"customBreakdownGroup":{"id":73,"name":"Ungrouped"}}]'),
(14,'AW','[{"customBreakdown":{"id":15,"name":"By PO"},"customBreakdownGroup":{"id":72,"name":"team"}},{"customBreakdown":{"id":14,"name":"By SOW/PO"},"customBreakdownGroup":{"id":73,"name":"Ungrouped"}}]')
 ,
(15,'AW','[]')--,
  ;

-- SELECT * FROM a;

My query (which requires a fix):

SELECT 
  code
--  ,custom
  ,arr.item_object->'customBreakdown'->>'name' as customBreakdownName
  ,arr.item_object->'customBreakdownGroup'->>'name' as customBreakdownGroupName
FROM a
left join jsonb_array_elements(a.custom::JSONB) with ordinality arr(item_object) on true
where arr.item_object->'customBreakdown'->>'name' = 'By SOW'
  and arr.item_object->'customBreakdownGroup'->>'name' != 'Ungrouped'
--  or arr.item_object->'customBreakdown'->>'name' != 'By SOW'
--  and 
-- or a.custom::jsonb = '[]'

It requires fix because returns different result:

code	customBreakdownName	customBreakdownGroupName
GOOG	By SOW	poland
GOOG	By SOW	team

The column custom should be defined as jsonb, not as text — user330315
– user330315, Commented Dec 22, 2022 at 11:58
@a_horse_with_no_name, I provided table fields very close to original ones. The type is the same there. That's not an issue, but if you are more comfortable to provide solution where custom is jsonb type, you are welcome! I'll accept any correct answer. — Serhii
– Serhii, Commented Dec 22, 2022 at 12:05
I don't understand the condition why AW should be returned - presumably the row with ID = 15 but not the row with ID = 5 where the custom column is also an empty array — user330315
– user330315, Commented Dec 22, 2022 at 12:12
@a_horse_with_no_name, the row with ID=5 (like 3 or 4) should be skipped because we met first condition combination, in case first condition combination doesn't have any match, we need manage second condition part. — Serhii
– Serhii, Commented Dec 22, 2022 at 12:27

dimnnv · Accepted Answer · 2022-12-22 12:07:52Z

1

I would go with multiple "with" statements:

first flatten the array in JSONB
then find all unique codes
then filter flattened rows based on your condition
then join unique codes with matching rows to get the result.

WITH flattened AS (SELECT 
  code,
  jsonb_array_elements(custom::jsonb) as cust
FROM a),
all_codes AS (SELECT DISTINCT code FROM a),
matching AS (SELECT code,
  cust->'customBreakdown'->'name' AS customBreakdown,
  cust->'customBreakdownGroup'->'name' AS customBreakdownGroup
FROM flattened
WHERE cust->'customBreakdown'->'name' = '"By SOW"' AND cust->'customBreakdownGroup'->'name' != '"Ungrouped"')
SELECT 
  all_codes.code,
  matching.customBreakdown,
  matching.customBreakdownGroup
FROM all_codes
LEFT JOIN matching ON all_codes.code = matching.code

Fiddle: https://dbfiddle.uk/AFzoJwfS

answered Dec 22, 2022 at 12:07

dimnnv

8723 gold badges10 silver badges23 bronze badges

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

2 Comments

Serhii Over a year ago

I really confused. It works correctly but i have no idea why (as i remember standard query starts with select * from ...). Is it single query? In case my query will be more complex because of performance optimisation may I have WITH flattened inside of join?

dimnnv Over a year ago

In Postgresql you can use "with" (aka common table expressions) to produce somewhat like "temporary" tables from which the latter queries can fetch the data. Actually I'm not sure if it is the most efficient way in terms of used RAM, but it looks like linear scan of table so no N^2 or other irregularities expected. Also I'm sure the task is also achievable without "with" but I can't figure how off top of my head.

Collectives™ on Stack Overflow

PostgreSQL query filtering results by conditions based on jsonb

P.S.

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

P.S.

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related