Two OPENJSON, return each array element in separate rows



  • 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.

    https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=464d87fbd2f496baf0fc68ffcc6ec788

    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



  • 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
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2