Argthtjtr

I have the below code in JSON file :

{

    "took": 196,

    "timed_out": false,

    "_shards": {

        "total": 15,

        "successful": 15,

        "failed": 0

                },

    "hits": {

        "total": 165,

        "max_score": null,

        "hits": [

            {

                "_index": "logstash-2018.11.22",

                "_type": "nagios_core",

                "_id": "AWc6C_EtHRYvW4hmI7sl",

                "_score": null,

                "_source": {

                 "message": "EXTERNAL COMMAND:  ACKNOWLEDGE_SVC_PROBLEM;DE-Hoeheinoed-VOC1-SRV;ntp_timesync;2;0;0;Jaizel Jem Perdon;SN 307185410",

                    "@version": "1",

                    "@timestamp": "2018-11-22T06: 12: 00.307Z",

                    "host": "172.26.66.59",

                    "port": 44154,

                    "type": "nagios_core",

                    "epoch_timestamp": "1542867118",

                    "nagios_severity_label": "EXTERNAL COMMAND",

                    "nagios_external_command": "ACKNOWLEDGE_SVC_PROBLEM",

                    "nagios_host": "DE-Hoeheinoed-VOC1-SRV",

                    "nagios_service": "ntp_timesync",

                    "nagios_sticky": "2",

                    "nagios_notify": "0",

                    "nagios_persistent": "0",

                    "nagios_author": "Jaizel Jem Perdon",

                    "nagios_comment": "SN 307185410",

                    "utc_timestamp": "2018-11-22T06: 11: 58.000Z"

                },

                "sort": [

                    1542867120307

                ]

            }  



        ]

    }

}

And i have the below code in SQL: However, I am getting null values in my result. As am new to JSON, am not able to find out the path of the JSON data values

Drop table if exists  #Temp1

Declare @JSON nvarchar(max)

SELECT @JSON = BulkColumn

FROM OPENROWSET (BULK '\DKRDSDFSROOT10Data_TempMEIPEITE1452552_02test.json', SINGLE_CLOB) as j





select @json as details





If (ISJSON(@json) = 1)

BEGIN 

  PRINT 'JOSN File is valid';



  select * into #Temp1

    from OPENJSON(@JSON, '$.hits')

 WITH 

     ( 

  [nagios_author] nvarchar(100) '$.hits.hits._source.nagios_author',

  [nagios_comment] nvarchar(100)   '$.hits.hits._source.nagios_comment'  

  )



  END 

  ELse



  Begin 

  PRINT 'JOSN File is invalid';



  END 





  select * from #Temp1

Can someone please help me ?

You are using the second hits array in your json as an object, but its an array try to change your query as follows:

 select * into #Temp1

    from OPENJSON(@JSON, '$.hits')

 WITH 

     ( 

  [nagios_author] nvarchar(100) '$.hits.hits[0]._source.nagios_author',

  [nagios_comment] nvarchar(100)   '$.hits.hits[0]._source.nagios_comment'  

  )

I tried using below code and it worked

WITH
(
[nagios_author] nvarchar(100) '$.hits[0]._source.nagios_author',
[nagios_comment] nvarchar(100) '$.hits[0]._source.nagios_comment'

)