Question 281771 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

Parse multilevel json into a csv using jq tool in unix

I have a multilevel json like so:

{
    "id": "id123",
    "details": {
        "prod": "prod123",
        "etype": "type1"
    },
    "accounts": [
        {
            "bankName": "bank123",
            "accountType": "account123",
            "openingBalance": "bal123",
            "fromDate": "2023-01-01",
            "toDate": "2023-01-01",
            "missingMonths": [],
            "transactions": [
                {
                    "dateTime": "2020-12-01",
                    "description": "a very long string",
                    "amount": -599.0,
                    "bal": 8154.83,
                    "type": "Debit"
                },
                {
                    "dateTime": "2020-12-01",
                    "description": "a very long string; a very long string",
                    "amount": -4000.0,
                    "balanceAfterTransaction": 4154.83,
                    "type": "Debit"
                }
            ]
        }
    ],
    "accountid": "sample123"
}

I want to parse it to a csv in the following format:

id,prod,etype,bankName,accountType,openingBalance,fromDate,toDate,dateTime,description,amount,bal,type,accountid
id123,prod123,type1,bank123,account123,bal123,2023-01-01,2023-01-01,2020-12-01,a very long string,-599.0,8154.83,Debit,sample123
id123,prod123,type1,bank123,account123,bal123,2023-01-01,2023-01-01,2020-12-01,a very long string; a very long string,-4000.0,4157.83,Debit,sample123

Parse the transactions into a proper csv and repeat the common info for each transaction.

I have gotten only so far yet:

jq --raw-output '[ .id, .details[], .accounts[].transactions[] ]'

Tags:

2

Answers


  1. 0

    This will help you get the desired CSV format:

    jq -r '.[] | "(.id),(.details.prod),(.details.etype),(.accounts[].bankName)"'

    you can build further on it to get all desired values..

    Note: For a valid CSV though, you might need to add double-quotes around some values, like strings

    to parse multiple JSON entries, they are expected to be in an array:

    [
{
    "id": "id123",
    "details": {
        "prod": "prod123",
        "etype": "type1"
    },
    "accounts": [
        {
.....
        }
     ]
},
{
    "id": "id124",
    "details": {
        "prod": "prod124",
        "etype": "type1"
    },
    "accounts": [
        {
.....
        }
     ]
}
]
    Login or Signup to reply.
  2. 0

    You can save the outer data values in variables and get down progressively to the leaf nodes :

    jq -r '"id,prod,etype,bankName,accountType,openingBalance,fromDate,toDate,dateTime,description,amount,bal,type,accountid",
    ( . as {$id,$accountid}
    | .details as {$prod, $etype}
    | .accounts[]
    | . as { $bankName,$accountType,$openingBalance,$fromDate,$toDate }
    | .transactions[]
    | [$id,$prod,$etype,$bankName,$accountType,$openingBalance,$fromDate,$toDate,.dateTime,.description,.amount,.bal? // .balanceAfterTransaction,.type,$accountid]
    | join(","))' input.json
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search