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?

Answer 1

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