Parsing JSON with SQL (OPENJSON)

Question

You can download your Google searches and they come in the form of several JSON files. I'm trying to parse them into the columns [TimeStamp] and [Query Text] with the SQL function OPENJSON

DECLARE @json as nvarchar(max)

SET @json = N'{"event":[{"query":{"id":[{"timestamp_usec":"1329003091748889"}],"query_text":"download google searches"}}]}'

SELECT * FROM OPENJSON(@json, N'$.event')
WITH (   
      [TimeStamp] nvarchar(max) N'$.query.timestamp_usec'   ,
      [QueryText]   nvarchar(max) N'$.query.query_text'   
)

Query text returns the result, but the timestamp simply reads NULL. What am I doing wrong?

Suvethan Nantha · Accepted Answer · 2017-09-29 09:26:05Z

9

There is small fault in your code.

Sample Json

{
    "event": [{
        "query": {
            "id": [{
                "timestamp_usec": "1506676658"
            }],
            "query_text": "download google searches"
        }
    }]
}

If you take sample json timestamp_usec is inside the id array, so in order to get the data from that you need to do a small modification to query as shown below.

DECLARE @json as nvarchar(max)

SET @json = N'{"event":[{"query":{"id":[{"timestamp_usec":"1506676658"}],"query_text":"download google searches"}}]}'

SELECT * FROM OPENJSON(@json, N'$.event')
WITH (   
      [TimeStamp] nvarchar(max) N'$.query.id[0].timestamp_usec'   ,
      [QueryText]   nvarchar(max) N'$.query.query_text'   
)

answered Sep 29, 2017 at 9:26

Suvethan Nantha

2,47418 silver badges30 bronze badges

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

1 Comment

timlohnes Over a year ago

Thanks, Suvethan. I read countless examples of this, but could never quite tweak it to the dataset that was returned from an API. This made clear sense and worked perfectly.

Collectives™ on Stack Overflow

Parsing JSON with SQL (OPENJSON)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related