In Postgres 12.3 I am having trouble building the correct SQL query that can join a table from a deeply nested array in a jsonb object with rows from another table.
The deeply nested array contains strings and optionally an array of strings. These arrays should be flattened and then used to join in the other table.
Here is a DB Fiddle with the below data ready to test: https://www.db-fiddle.com/f/6tMMNn3JsgkbehCptw4xEx/0
Details
I need a SQL query given the below tables that will return from order_batches
the batches which contain orders where state does not equal 'picked' or 'packed'. With the given table data below only order_batches
with ID 1
should be returned.
Essentially I need the query to look at each row in order_batches
, for all the tags->'bins'
arrays, find and flatten any order_numbers
, and join (or sub-select?) the orders
table on those order_numbers
. The where clause should retrieve only those batches that include an order with a state that does not match 'picked' or 'packed'.
Expressing this in pseudo sql might look like:
SELECT order_batches.*
FROM order_batches
JOIN orders ON order_batches.tags->'bins'->__all array elements__->'order_numbers' = orders.number
WHERE orders.state IS NOT IN ('picked', 'packed')
Desired Output
The output that I am looking for is equal to the result of this query:
SELECT * from order_numbers WHERE id = 1
╔════╦══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╗
║ id ║ tags ║
╠════╬══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╣
║ 1 ║ {"bins":[{"order_numbers":["555111111","555111112",["555111114","555111115"]]},{"order_numbers":["555111116","555111117"]}]} ║
╚════╩══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╝
Resources
I have tried searching for an answer, but did not find one exactly like mine. These results have helped, but I'm still not sure how to get it right:
- https://stackoverflow.com/questions/49766873/how-to-join-on-the-values-returned-from-json-array-elements-in-postgresql
- PostgreSql : flatten json array data
Test Data
Here is the db schema and test data:
create table "order_batches" (id bigint primary key, tags jsonb);
insert into "order_batches"
(id, tags)
values
(1, '{
"bins": [
{
"order_numbers": [
"555111111",
"555111112",
[
"555111114",
"555111115"
]
]
},
{
"order_numbers": [
"555111116",
"555111117"
]
}
]
}'),
(2, '{
"bins": [
{
"order_numbers": [
"555111118",
"111111111"
]
}
]
}');
create table "orders" (id bigint primary key, "number" varchar(255), state varchar(255));
INSERT INTO "orders" (id, "number", state) VALUES
(1, '555111111', 'picked'),
(2, '555111112', 'picked'),
(3, '555111114', 'picked'),
(4, '555111115', 'ready'),
(5, '555111116', 'ready'),
(6, '555111117', 'ready'),
(7, '555111118', 'picked'),
(8, '111111111', 'packed');
Attempts
Here are some queries I have tried in my attempt to get it working and to understand the problem better:
SELECT ob.id, j.nums->'order_numbers'
FROM order_batches ob
CROSS JOIN lateral jsonb_array_elements(tags->'bins') j(nums)
JOIN orders o on o.number = j.nums->'order_numbers'
WITH numbers AS(
SELECT jsonb_array_elements("tags"->'bins') AS numbers FROM order_batches
)
SELECT * FROM numbers JOIN orders o ON o.number = numbers.numbers
SELECT
case jsonb_typeof(numbers)
when 'string' then numbers->>0
when 'array' then jsonb_array_elements_text(numbers)
end as number
FROM order_batches, jsonb_array_elements(order_batches.tags->'bins') as numbers
Thanks in advance for any help with this problem. At this point I do not think it would be worth revising the data structure. I'm hoping a SQL expert can whip up some magic to help me (and others) out here.
order_numbers
array (id = 1 in the sample data). That looks like a really ugly way of storing things. It would be way easier if all elements of theorder_numbers
array were always the same typeorder_numbers
is ugly, but it does serve a purpose in our system. This allows us to group orders together that are being shipped to the same location, ensuring they end up together. There is probably a better way to model that, but that is why it is. I will edit to show desired output, basicallySELECT * from order_batches where id = 1
is equivalent to the desired output (given this dataset). Thanks for your help!order_numbers
in a second key on that jsonb object. I will revise the question. Thanks again for your help!