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