Two OPENJSON, return each array element in separate rows

by

The 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

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

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


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 Comment