How to extract fields with JSON values while creating a DB input in Splunk DB Connect?

[gsrivastava]

• I am creating a DB Input in DB Connect v3
• My DB columns contains JSON values.
• I am getting correct raw data in Splunk, but on selecting Table mode, the field does not have correct values. for ex, if the column name is status and the JSON it contains is : "[{"datasetId":1,"refreshStatus":16}]", then the field created in splunk is status but it only contains the value : '[{'.
• All the rows in Splunk contains this same value '[{'
• May be Splunk is using double quote as a delimiter to separate fields, but the JSON itself contains the quotes.

[stefan_d]

Hi there!

Have you managed to solve this problem?
I also have a similar issue, is it possible to grab the JSON data from a column in your DB input?

[gsrivastava]

May be this happens because Splunk IFX uses comma(,) as a default delimiter to separate and extract fields but the JSON itself contains commas(,) , therefore the default regex which is generated separates the field when it encounters the first comma(,) .
So to extract any JSON field, one has to edit the original regex and make any changes required to extract the entire JSON field instead of '[}'

[burwell]

Hello gsrivastava . I am seeing my fields with the JSON. Try SELECT field and then do | table field

I am seeing {"key":"value,"key2":"value2"} etc.

Maybe you can show your SELECT with LIMIT 1 and the output you get.

[burwell]

I am using DB Connect 2.4.0 with JSON data. A sample might be.

"record":{"subfield1":"value1","subfield2","value2"}

I use "SELECT record['subfield1'] AS subfield1.. WHERE record['subfield'] IS NOT NULL" to get the value.

And if subfield1 itself is JSONlike then I do something like rex field=subfield1 "\"somefield\":(?<somefieldT>.*?)[,}]" in order to get somefield out.

[gsrivastava]

Thanks @burwell for responding. But I want complete json in a field and handle that json in splunk itself.
Sample JSON array is-

[  
   {  
      "set":3,
      "ids":[  
         9
      ],
      "status":12,
      "code":570,
      "idDetails":[  
         {  
            "id":9,
            "status":12,
            "code":570
         }
      ]
   },
   {  
      "set":1,
      "ids":[  
         5,
         8
      ],
      "status":21,
      "code":601,
      "idDetails":[  
         {  
            "id":5,
            "status":21,
            "code":601
         },
         {  
            "id":8,
            "status":21,
            "code":601
         }
      ]
   }
]

With this kind of nested JSON and with multiple keys having the same name, its not possible to extract all the fields in SQL itself.