parsing/extract json data with jq

Question

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

The JSON you give us needs to be well-formed and complete enough to provide the desired output. No ..., commas only where it's legal to have them, etc. — Charles Duffy
– Charles Duffy, Commented Oct 11, 2022 at 13:12
and really, the payload field absolutely needs to be rewritten. — Charles Duffy
– Charles Duffy, Commented Oct 11, 2022 at 13:16
Is the .payload field supposed to be an object or a JSON-encoded string? — pmf
– pmf, Commented Oct 11, 2022 at 13:36

Charles Duffy · Accepted Answer · 2022-10-11 13:26:50Z

3

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

answered Oct 11, 2022 at 13:26

Charles Duffy

299k43 gold badges441 silver badges496 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

pmf · Accepted Answer · 2022-10-11 13:56:13Z

2

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

edited Oct 11, 2022 at 13:56

answered Oct 11, 2022 at 13:46

pmf

38.3k3 gold badges31 silver badges41 bronze badges

Collectives™ on Stack Overflow

parsing/extract json data with jq

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related