Stored procedure: how to get data from JSON object in multi array

Question

I try to get data from JSON with stored procedures and I have data in JSON format.

This is my data:

{
  "data": [
    {
      "@attributes": {
        "id": "123456",
        "name": "test"
      }
    },
    {
      "prices": {
        "price": [
          {
            "@attributes": [
              {
                "date": "2019-06-13",
                "price": "600",
                "currency": "$"
              },
              {
                "date": "2019-06-15",
                "price": "700",
                "currency": "$"
              }
            ]
          }
        ]
      }
    },
    {
      "images": {
        "image": [
          {
            "@attributes": [
              {
                "date": "2019-06-13",
                "url": "xxxxx"
              }
            ]
          }
        ]
      }
    }
  ]
}

and I try will get data price in JSON by get "id": "123456" is PK of price

This my stored procedure:

INSERT @price_tmp (id, date, price, currency) 
    SELECT id, date, price, currency 
    FROM OPENJSON(@InputJson,'$.data.prices.price')
            WITH (
                  id nvarchar(50) '../../$."@attributes".id',
                  price_date nvarchar(255) '$."@attributes".date', 
                  price_price nvarchar(50) '$."@attributes".price', 
                  price_currency nvarchar(255) '$."@attributes".currency'
                 ) AS jsonValues`

after I do

select * from @price_tmp;

I expect to see data like this:

ID     |   date     |  price  | currency
-------+------------+---------+---------
123456 | 2019-06-13 |  600    |       $
123456 | 2019-06-15 |  700    |       $

But it does not show anything

I can use Answer both

https://stackoverflow.com/a/57967971/8556614

https://stackoverflow.com/a/57967997/8556614

Thank you metal and Zhorov

Ed Bangga · Accepted Answer · 2019-09-17 05:43:59Z

1

it seems your json is not anymore the correct one based on your query. consider changing how you parse your json data.

INSERT @price_tmp (id,date,price,currency) 
SELECT t1.id, t1.price_date, t1.price_price, t1.price_currency FROM (
    SELECT JSON_Value (c.[value], '$."@attributes".id') as id 
        ,  jsonValues.price_date
        ,  jsonValues.price_price
        ,  jsonValues.price_currency
    FROM OPENJSON(@InputJson,'$.data[1].prices.price[0]."@attributes"')
    WITH (  
        price_date nvarchar(255) '$.date', 
        price_price nvarchar(50) '$.price', 
        price_currency nvarchar(255) '$.currency'
    ) AS jsonValues
    CROSS APPLY OPENJSON(@InputJson, '$.data') c) t1
WHERE t1.id is not null

answered Sep 17, 2019 at 5:43

Ed Bangga

13k4 gold badges18 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

story ks Over a year ago

In SELECT t1.id, t1.price_date, t1.price_price, t1.price_currency I will DECLARE ?

Ed Bangga Over a year ago

@storyks no need to declare. replace your entire insert script. this should do it.

story ks Over a year ago

It no data after I select*from @price_tmp

Ed Bangga Over a year ago

your @price_tmp is temporary table, you need to create it first

story ks Over a year ago

Yes, I create @price_tmp before insert

|

Zhorov · Accepted Answer · 2019-09-17 10:59:58Z

1

This is a complex JSON data, but if the structure of this JSON is fixed, then the next approach may help. You need to use JSON_VALUE() to get the id and OPENJSON() with explicit schema definition to get the prices data.

JSON:

DECLARE @InputJson nvarchar(max) = N'{
  "data": [
    {
      "@attributes": {
        "id": "123456",
        "name": "test"
      }
    },
    {
      "prices": {
        "price": [
          {
            "@attributes": [
              {
                "date": "2019-06-13",
                "price": "600",
                "currency": "$"
              },
              {
                "date": "2019-06-15",
                "price": "700",
                "currency": "$"
              }
            ]
          }
        ]
      }
    },
    {
      "images": {
        "image": [
          {
            "@attributes": [
              {
                "date": "2019-06-13",
                "url": "xxxxx"
              }
            ]
          }
        ]
      }
    }
  ]
}'

Statement:

DECLARE @price_tmp TABLE(
   id nvarchar(50),
   price_date nvarchar(255), 
   price_price nvarchar(50), 
   price_currency nvarchar(255)
)   

INSERT INTO @price_tmp   
SELECT 
   JSON_VALUE(@InputJson, '$.data[0]."@attributes".id') AS ID,
   j.*
FROM OPENJSON(@InputJson, '$.data[1].prices.price[0]."@attributes"') WITH (
   price_date nvarchar(255) '$.date', 
   price_price nvarchar(50) '$.price', 
   price_currency nvarchar(255) '$.currency'
) j

SELECT *
FROM @price_tmp

Output:

------------------------------------------
ID      price_date  price_price price_currency
------------------------------------------
123456  2019-06-13  600         $
123456  2019-06-15  700         $

edited Sep 17, 2019 at 10:59

answered Sep 17, 2019 at 5:46

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

2 Comments

story ks Over a year ago

It no data after I select*from @price_tmp

story ks Over a year ago

i use EXEC Sync_Resource_test @InputJson = JSON from My question it error Msg 13607, Level 16, State 3, Procedure Sync_Resource_test, Line 317 [Batch Start Line 0] The format of the JSON path is not correct. An unexpected character ". Is at position 0.

Collectives™ on Stack Overflow

Stored procedure: how to get data from JSON object in multi array

2 Answers 2

7 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related