1

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:

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.

4
  • Do you really have a nested array inside the 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 the order_numbers array were always the same type
    – user1822
    Jun 25, 2020 at 5:59
  • @a_horse_with_no_name I am using Postgres 11.4, sorry I thought I mentioned that, but I will edit it. The nested array inside order_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, basically SELECT * from order_batches where id = 1 is equivalent to the desired output (given this dataset). Thanks for your help!
    – ianneub
    Jun 25, 2020 at 14:29
  • Roger that @a_horse_with_no_name. I could store the simple array of order_numbers in a second key on that jsonb object. I will revise the question. Thanks again for your help!
    – ianneub
    Jun 25, 2020 at 14:38
  • That's awesome! Thanks so much @a_horse_with_no_name. I have no problem upgrading to PostgreSQL 12. I'll update my question. If you'd like to submit your example as an answer that definitely solves it for me. Thanks again!
    – ianneub
    Jun 25, 2020 at 14:46

1 Answer 1

1

This is relatively easy to solve in Postgres 12, because the new SQL JSON/Path feature does the heavy lifting of iterating through (nested) arrays.

SELECT ob.*
FROM  order_batches ob
where exists (select *
              from orders ord
              where jsonb_path_exists(ob.tags, '$.bins[*].order_numbers[*] ? (@ == $nr)', jsonb_build_object('nr', ord.number))
              and ord.state NOT IN ('picked', 'packed'));

The JSON path expression '$.bins[*].order_numbers[*] iterates through all array elements in the order_numbers array - including nested arrays. The jsonb_path_exists() then checks if there is a match with the number from the orders table.

The number is passed as a "json parameter" to the jsonb_path_exists() function and the $nr placeholder references that. The name of that parameter must match the key of the JSON value passed as the third parameter of the jsonb_path_exists function.

I don't think this is going to be very fast though as the way the jsonb_path_exists() function is used, can't make use of an index as far as I know.

Online example

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.