Sample Header Ad - 728x90

Two OPENJSON, return each array element in separate rows

3 votes
2 answers
5106 views
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
Asked by Mattia (33 rep)
Mar 15, 2022, 09:41 PM
Last activity: Mar 17, 2022, 03:37 AM