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

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:

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:

CodePudding user response：

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