Two OPENJSON, return each array element in separate rows

All we need is an easy explanation of the problem, so here it is.

I have a table where I save in JSON format the quantity that exit and return of a product.
The data saved in the two columns out_quantity and in_quantity is in array format.

CREATE TABLE products_info (
  product_id int NOT NULL,
  out_quantity varchar(4000) DEFAULT '[]',
  in_quantity varchar(4000) DEFAULT '[]'
 );
product_id out_quantity in_quantity
1 ‘[{"date":"2022-03-01","quantity":10}, {"date":"2022-03-02","quantity":20}]’ ‘[{"date":"2022-03-15","quantity":30}]’

Using this query

SELECT product_id, o.out_date, o.out_quantity, i.in_date, i.in_quantity FROM products_info
CROSS APPLY OPENJSON (
      out_quantity
  ) WITH (
      out_date date '$.date',
      out_quantity int '$.quantity'
  ) o
CROSS APPLY OPENJSON (
      in_quantity
  ) WITH (
      in_date date '$.date',
      in_quantity int '$.quantity'
  ) i;

This is what I get

product_id out_date out_quantity in_date in_quantity
1 2022-03-01 10 2022-03-15 30
1 2022-03-02 20 2022-03-15 30

But what I’m trying to achieve is not to have duplicated data like this

product_id out_date out_quantity in_date in_quantity
1 2022-03-01 10 NULL NULL
1 2022-03-02 20 NULL NULL
1 NULL NULL 2022-03-15 30

I know this is expected behaviour using cross apply but I couldn’t find any other solutions.

dbfiddle

I have also other columns that I get from JOINs like product_description that I get from table products. I haven’t added them in this example

Thanks

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You can do this with only a single scan of the main table.

SELECT pi.product_id, o.*
FROM products_info pi
CROSS APPLY (
    SELECT o.out_date, o.out_quantity, NULL in_date, NULL in_quantity
    FROM OPENJSON (
          pi.out_quantity
      ) WITH (
          out_date date '$.date',
          out_quantity int '$.quantity'
    ) o
    UNION ALL
    SELECT NULL, NULL, o.in_date, o.in_quantity
    FROM OPENJSON (
          pi.in_quantity
      ) WITH (
          in_date date '$.date',
          in_quantity int '$.quantity'
    ) o
) o;

Method 2

If you want to see separate row per in and out then use UNION ALL

SELECT product_id, o.out_date date, o.out_quantity quantity, 'out' As Direction
FROM products_info
CROSS APPLY OPENJSON (
      out_quantity
  ) WITH (
      out_date date '$.date',
      out_quantity int '$.quantity'
  ) o
  Union ALl
  SELECT product_id, o.in_date, o.in_quantity, 'in'
FROM products_info
CROSS APPLY OPENJSON (
      in_quantity
  ) WITH (
      in_date date '$.date',
      in_quantity int '$.quantity'
  ) o

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply