parsing/extract json data with jq-CodePudding

Please help me to extract json data. i tried to fetch the data with some jq queriesm but results came as line-by-line

cat test.json
..
{
  "took" : 43,
  "timed_out" : false,
  "cardss" : {
    "values" : 0,
    "faileds" : 0
  },
 "counts" : {
    "total" : 200,
    "max_hint" : 1.0000004,
    "counts" : [
      {
        "_index" : "test_90.008.",
        "_type" : "fluentdd",
        "_id" : "SLSLSLSLSLLSdfsdhjdshfdshfdshfkjdsfdsfsfdsf",
        "_score" : 1.0000004,
        "_source" : {
          "payload" : """{"ID":"11390","Key":"SKSKDISKSK","paymentId":"LSDLSLS-LSLSLSLs-KGOGK","bunkoinfo":{"janaluID":"918282827","ipAddress":"0.0.0.0","chethiid":"fkfkfkfkfkfkfkfkfkkf"},"dabbulluInfo":{"checkType":"mundhucheck","currency":"INR","method":"paper","motthamAmount":"331","cards":{"cardsToken":"2021000","upicodes":"331","cardchettha":"6739837","digitcardss":"0000","kaliDate":"00000"}},"PackOrdetls":[{"items":[{"itemName":"00","quantity":"0","price":"331"}]}],"dtdcid":"kskdkskdsjsjsjdososlsksj"}"""
        }
      },

  }
}

required output is below, please support.

Id,paymentId,motthamAmount,curreny
11390,LSDLSLS-LSLSLSLs-KGOGK,331,INR

i tried

cat test.json  | jq -r '.counts.counts[]._source.payload.ID, .counts.counts[]._source.payload.paymentId, .counts.counts[]._source.payload.dabbulluInfo.motthamAmount, .counts.counts[]._source.payload.dabbulluInfo.currency'

got output as one-by-one 


11390
LSDLSLS-LSLSLSLs-KGOGK
331
INR

CodePudding user response：

If we rewrite your data until it's actually valid, an answer might look like:

jq -rn '
([ "Id", "paymentId", "motthamAmount", "currency" ] | @csv),
(inputs | .counts.counts[] | [
  ._source.payload.ID, 
  ._source.payload.paymentId, 
  ._source.payload.dabbulluInfo.motthamAmount, 
  ._source.payload.dabbulluInfo.currency
] | @csv)
' <test.json

See this functioning at https://replit.com/@CharlesDuffy2/RequiredInfiniteComment#main.sh

CodePudding user response：

Here's another attempt trying to reuse traversal:

jq -r '
  ["Id", "paymentId", "motthamAmount", "curreny"], (
    .counts.counts[]._source.payload
    | [.ID, .paymentId, (.dabbulluInfo | .motthamAmount, .currency)]
  ) | @csv
'

"Id","paymentId","motthamAmount","curreny"
"11390","LSDLSLS-LSLSLSLs-KGOGK","331","INR"

Demo