locked
Need help with OPENJSON Syntax RRS feed

  • Question

  • Hi,

    I've been playing around with the new OPENJSON available in SQL 2016. Specifically using the output from Google's Drivetime API.

    Basically I have a JSON file output that I want to put into a SQL table, but no matter what syntax I try it doesn't seem to be working.

    This is my sample JSON file contents:

    {


       "destination_addresses"

    :[ "Orlando, FL, USA" ],


       "origin_addresses"

    :[ "New York, NY, USA" ],


       "rows"

    :[

          {

             "elements" : [

                {

                   "distance" : {

                      "text" : "1,072 mi",

                      "value" : 1725756

                   },

                   "duration" : {

                      "text" : "15 hours 40 mins",

                      "value" : 56405

                   },

                   "status" : "OK"

                }

             ]

         

    }


       ]

    ,


       "status"

    :"OK"


    }

    What I want is a SQL table that has 2 columns, column 1 will be item names (Destination Address,Origin Address, Distance Text, Distance Value, Duration Text & Duration Value) and column 1 will be the actual data.

    I can get OPENJSON to do what I want for destination address & origin address but when I try and using OPENJSON to read the nested items that exist in 'rows - > elements' I can't seem to pick them up. Can anyone help with the right syntax?

    Thanks :)

    Sunday, May 28, 2017 9:19 AM

Answers

  • Is this what you are looking for:

    declare @str varchar(4000) = 
    N'{
       "destination_addresses":[
          "Orlando, FL, USA"
       ],
       "origin_addresses":[
          "New York, NY, USA"
       ],
       "rows":[
          {
             "elements":[
                {
                   "distance":{
                      "text":"1,072 mi",
                      "value":1725756
                   },
                   "duration":{
                      "text":"15 hours 40 mins",
                      "value":56405
                   },
                   "status":"OK"
                }
             ]
          }
       ],
       "status":"OK"
    }'
    
    SELECT 
    	CONCAT_WS(' ',Destination_Addresses, Origin_Addresses,Distance_Text,Distance_Value,Duration_Text,Duration_Value) as Col1
    	,@str as Col2
    FROM OPENJSON (@str)
    WITH (
        Destination_Addresses    VARCHAR(1000) '$.destination_addresses[0]',
        Origin_Addresses   VARCHAR(1000) '$.origin_addresses[0]',
        Distance_Text    VARCHAR(1000) '$.rows[0].elements[0].distance.text',
        Distance_Value   VARCHAR(1000) '$.rows[0].elements[0].distance.value',
        Duration_Text    VARCHAR(1000) '$.rows[0].elements[0].duration.text',
        Duration_Value   VARCHAR(1000) '$.rows[0].elements[0].duration.value'
    ) AS Orders

    Check here how to use JSON with SQL in many ways: https://sqlwithmanoj.com/tag/json/


    ~manoj | SQLwithManoj.com

    Sunday, May 28, 2017 6:30 PM

All replies

  • I actually surprised that you got anything at all since there is no query.  You are more likely to get a helpful response if you provide what you have tried so far.


    Russel Loski, MCSE Data Platform/Business Intelligence Twitter: @sqlmovers; blog: www.sqlmovers.com

    Sunday, May 28, 2017 11:53 AM
  • Sorry.............I don't have access to my original attempt as it is on a server I am not able to access at the moment so I thought I'd just see if people could provide some insight with what I had to hand.

    Next time I will wait until my server is available again......

    Sunday, May 28, 2017 12:51 PM
  • Thanks. 

    I think I've tried those but will have another read through - I prob missed something obvious

    Sunday, May 28, 2017 12:51 PM
  • Is this what you are looking for:

    declare @str varchar(4000) = 
    N'{
       "destination_addresses":[
          "Orlando, FL, USA"
       ],
       "origin_addresses":[
          "New York, NY, USA"
       ],
       "rows":[
          {
             "elements":[
                {
                   "distance":{
                      "text":"1,072 mi",
                      "value":1725756
                   },
                   "duration":{
                      "text":"15 hours 40 mins",
                      "value":56405
                   },
                   "status":"OK"
                }
             ]
          }
       ],
       "status":"OK"
    }'
    
    SELECT 
    	CONCAT_WS(' ',Destination_Addresses, Origin_Addresses,Distance_Text,Distance_Value,Duration_Text,Duration_Value) as Col1
    	,@str as Col2
    FROM OPENJSON (@str)
    WITH (
        Destination_Addresses    VARCHAR(1000) '$.destination_addresses[0]',
        Origin_Addresses   VARCHAR(1000) '$.origin_addresses[0]',
        Distance_Text    VARCHAR(1000) '$.rows[0].elements[0].distance.text',
        Distance_Value   VARCHAR(1000) '$.rows[0].elements[0].distance.value',
        Duration_Text    VARCHAR(1000) '$.rows[0].elements[0].duration.text',
        Duration_Value   VARCHAR(1000) '$.rows[0].elements[0].duration.value'
    ) AS Orders

    Check here how to use JSON with SQL in many ways: https://sqlwithmanoj.com/tag/json/


    ~manoj | SQLwithManoj.com

    Sunday, May 28, 2017 6:30 PM
  • Hi TheWake,

    According to your description and sample data, you may check if the following script helps.

    declare @json nvarchar(max) = '
    {
      "destination_addresses": [
        "Orlando, FL, USA"
      ],
      "origin_addresses": [
        "New York, NY, USA"
      ],
      "rows": [
        {
          "elements": [
            {
              "distance": {
                "text": "1,072 mi",
                "value": 1725756
              },
              "duration": {
                "text": "15 hours 40 mins",
                "value": 56405
              },
              "status": "OK"
            }
          ]
        }
      ],
      "status": "OK"
    }
    ';
    
    ;WITH CTE([Destination Address], [Origin Address], [Distance Text], [Distance Value], [Duration Text], [Duration Value]) AS
    (
    	SELECT JSON_VALUE(@json, '$.destination_addresses[0]')
    		, JSON_VALUE(@json, '$.origin_addresses[0]')
    		, JSON_VALUE(b.[value], '$.distance.text')
    		, JSON_VALUE(b.[value], '$.distance.value')
    		, JSON_VALUE(b.[value], '$.duration.text')
    		, JSON_VALUE(b.[value], '$.duration.value')
    	FROM OPENJSON(@json, '$.rows') a
    	CROSS APPLY OPENJSON(a.[value], '$.elements') b
    )
    SELECT [name], [value]
    FROM CTE
    UNPIVOT ([value] FOR [name] IN ([Destination Address], [Origin Address], [Distance Text], [Distance Value], [Duration Text], [Duration Value])) AS unpvt

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, May 29, 2017 7:15 AM
  • That worked great - thanks Manoj
    Tuesday, May 30, 2017 7:33 AM