Two OPENJSON, return each array element in separate rows

Question

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

Answer 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;

Answer 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