Question 214776 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

How to use jq to convert json to csv from a complex json data

I’m new in using jq tool.
I have a json file extracted from mongodb database to be formatted into csv:

json data in foo.json

{
    "_id" : "8888888888",
    "unitcode" : "Unit123",
    "purpose" : "Validate",
    "request" : {
        "url" : "Brocolli",
        "method" : "UPDATED",
        "body" : {
            "bonds" : [
                {
                    "bondDesc" : "bondDesc1",
                    "priorPriceBid" : 100,
                    "priorPriceOffer" : 101,
                    "priorYieldBid" : 6.555,
                    "priorYieldOffer" : 6.777,
                    "updatedPriceBid" : 691,
                    "updatedPriceOffer" : 169,
                    "updatedYieldBid" : 25.2345,
                    "updatedYieldOffer" : 25.9999
                },
                {
                    "bondDesc" : "bondDesc2",
                    "priorPriceBid" : 200,
                    "priorPriceOffer" : 201,
                    "priorYieldBid" : 2.555,
                    "priorYieldOffer" : 2.777,
                    "updatedPriceBid" : 791,
                    "updatedPriceOffer" : 269,
                    "updatedYieldBid" : 35.2345,
                    "updatedYieldOffer" : 35.9999
                }
            ]
        },
        "source" : "Cloud",
        "isMultipleBond" : true
    },
    "response" : {
        "statusCode" : 200
    }
}

Desired output:

"UPDATED","Brocolli","bondDesc1",100,101,6.555,6.777,691,169,25.2345,25.9999
"UPDATED","Brocolli","bondDesc2",200,201,2.555,2.777,791,269,35.2345,35.9999

I already tried the following commands:

  1. first try

    jq -r '[.request.method, .request.url] | @csv'  foo.json

    Result:

    "bondDesc1",100

  2. next try

    jq -r ' .request.body.bonds[] | [.bondDesc, .priorPriceBid, .priorPriceOffer, .priorYieldBid, .priorYieldOffer, .updatedPriceBid, .updatedPriceOffer, .updatedYieldBid, .updatedYieldOffer] | @csv' foo.json

    Result:

    "bondDesc1",100,101,6.555,6.777,691,169,25.2345,25.9999
"bondDesc2",200,201,2.555,2.777,791,269,35.2345,35.9999

  3. But I can’t figure out on how to combine them, it just gave me an error:

    jq -r ' [.request.method, .request.url], .request.body.bonds[] | [.bondDesc, .priorPriceBid, .priorPriceOffer, .priorYieldBid, .priorYieldOffer, .updatedPriceBid, .updatedPriceOffer, .updatedYieldBid, .updatedYieldOffer] | @csv' foo.json

    Result:

    jq: error (at foo.json:40): Cannot index array with string "bondDesc"
Tags:

2

Answers


  1. 0

    Since the fields you want are at varying depths and in arrays, typical strategy is to collect the values you want at each level expanding any array separately then combine them all.

    Assuming you want the values in file order, you could just grab all values from the bond objects.

    .request | [.method, .url] + (.body.bonds[] | [.[]]) | @csv

    Though in general, you probably will want to explicitly pick out the values you want.

    .request | [.method, .url] + (.body.bonds[] | [
  .bondDesc,
  .priorPriceBid,
  .priorPriceOffer,
  .priorYieldBid,
  .priorYieldOffer,
  .updatedPriceBid,
  .updatedPriceOffer,
  .updatedYieldBid,
  .updatedYieldOffer
]) | @csv

    You had the right idea, but the parentheses around the expansion of the bonds array is important.

    jqplay

    Login or Signup to reply.
  2. 0

    As @Jeff_Mercado mentioned, you could potentially go with

    .request | [.method, .url] + (.body.bonds[] | [.[]]) | @csv

    but that assumes the ordering of keys within the objects is the same.
    Even if you believe that to be the case, it would be wise to adopt a more robust solution. Listing the keys would be one way to go, but that is tedious and not robust w.r.t. the addition or deletion of keys in the future.

    Assuming you want all the values corresponding to the keys in the first .bonds object, you could use the following:

    .request
| (.body.bonds[0] | keys_unsorted) as $keys
| [.method, .url] + (.body.bonds[] | [.[$keys[]]])
| @csv
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search