CODEWITHSUNDEEP
sqljson
PBeezy
PBeezy

Reputation: 1272

Parsing JSON with SQL (OPENJSON)

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?

Upvotes: 1

Views: 919

Answers (1)

Suvethan Nantha
Suvethan Nantha

Reputation: 2454

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'   
)

Upvotes: 9

Related Questions