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

Convert JSON to CSV, repeating parent value

There’s a lot of discussion to convert JSON to CSV with jq, but can’t find my situation.

2 points really blocking me :

  1. Get key and value of valuePerDay by row
  2. Sort valuePerDay keys to unsure values always in the same position

I assume this JSON give the valuePerDay always the same keys for each arr.

I have this JSON :

{
  "prop1": "val1",
  "prop2": "val2",
  "arr": [
    {
      "arrProp1": "arr1Prop1",
      "arrProp2": "arr1Prop2",
      "valuePerDay": {
        "2024-01-01": 1,
        "2024-01-02": 2,
        "2024-01-03": 3
      }
    },
    {
      "arrProp1": "arr2Prop1",
      "arrProp2": "arr2Prop2",
      "valuePerDay": {
        "2024-01-02": 22,
        "2024-01-01": 11,
        "2024-01-03": 33
      }
    },
    {
      "arrProp1": "arr3Prop1",
      "arrProp2": "arr3Prop1",
      "valuePerDay": {
        "2024-01-03": 333,
        "2024-01-01": 111,
        "2024-01-02": 222
      }
    }
  ]
}

Here is my actual JQ, but values are multiply by keys

.prop1 as $prop1
| .prop2 as $prop2
| .arr[]
  | .arrProp1 as $arrProp1
  | .arrProp2 as $arrProp2
  | .valuePerDay
    | keys_unsorted[] as $key
    | .[] as $value
| [$prop1,$prop2,$arrProp1,$arrProp2,$key,$value] 
| @csv

Which give me :

"prop1","prop2","arrProp1","arrProp2","valuePerDay","value"
"val1","val2","arr1Prop1","arr1Prop2","2024-01-01",1
"val1","val2","arr1Prop1","arr1Prop2","2024-01-01",2
"val1","val2","arr1Prop1","arr1Prop2","2024-01-01",3
"val1","val2","arr1Prop1","arr1Prop2","2024-01-02",1
"val1","val2","arr1Prop1","arr1Prop2","2024-01-02",2
"val1","val2","arr1Prop1","arr1Prop2","2024-01-02",3
"val1","val2","arr1Prop1","arr1Prop2","2024-01-03",1
"val1","val2","arr1Prop1","arr1Prop2","2024-01-03",2
"val1","val2","arr1Prop1","arr1Prop2","2024-01-03",3
"val1","val2","arr2Prop1","arr2Prop2","2024-01-01",11
"val1","val2","arr2Prop1","arr2Prop2","2024-01-01",22
"val1","val2","arr2Prop1","arr2Prop2","2024-01-01",33
"val1","val2","arr2Prop1","arr2Prop2","2024-01-02",11
"val1","val2","arr2Prop1","arr2Prop2","2024-01-02",22
"val1","val2","arr2Prop1","arr2Prop2","2024-01-02",11
"val1","val2","arr2Prop1","arr2Prop2","2024-01-03",33
"val1","val2","arr2Prop1","arr2Prop2","2024-01-03",22
"val1","val2","arr2Prop1","arr2Prop2","2024-01-03",33
"val1","val2","arr3Prop1","arr3Prop2","2024-01-01",111
"val1","val2","arr3Prop1","arr3Prop2","2024-01-01",222
"val1","val2","arr3Prop1","arr3Prop2","2024-01-01",333
"val1","val2","arr3Prop1","arr3Prop2","2024-01-02",111
"val1","val2","arr3Prop1","arr3Prop2","2024-01-02",222
"val1","val2","arr3Prop1","arr3Prop2","2024-01-02",333
"val1","val2","arr3Prop1","arr3Prop2","2024-01-03",111
"val1","val2","arr3Prop1","arr3Prop2","2024-01-03",222
"val1","val2","arr3Prop1","arr3Prop2","2024-01-03",333

And I want :

"prop1","prop2","arrProp1","arrProp2","valuePerDay","value"
"val1","val2","arr1Prop1","arr1Prop2","2024-01-01",1
"val1","val2","arr1Prop1","arr1Prop2","2024-01-02",2
"val1","val2","arr1Prop1","arr1Prop2","2024-01-03",3
"val1","val2","arr2Prop1","arr2Prop2","2024-01-01",11
"val1","val2","arr2Prop1","arr2Prop2","2024-01-02",22
"val1","val2","arr2Prop1","arr2Prop2","2024-01-03",33
"val1","val2","arr3Prop1","arr3Prop2","2024-01-01",111
"val1","val2","arr3Prop1","arr3Prop2","2024-01-02",222
"val1","val2","arr3Prop1","arr3Prop2","2024-01-03",333
Tags:

2

Answers


  1. 0

    With .[] as $value, you iterate again (after keys_unsorted[] as $key) over all items. Replace it with .[$key] as $value to just go into the one iterated over the first time:

    .prop1 as $prop1
| .prop2 as $prop2
| .arr[]
  | .arrProp1 as $arrProp1
  | .arrProp2 as $arrProp2
  | .valuePerDay
    | keys_unsorted[] as $key
    | .[$key] as $value
| [$prop1,$prop2,$arrProp1,$arrProp2,$key,$value] 
| @csv

    Demo

    Here’s a similar approach with simplifications for the variable binding, and the use of to_entries to get an array of key-value pairs:

    . as {$prop1, $prop2} | .arr[]
| . as {$arrProp1, $arrProp2} | .valuePerDay | to_entries[]
| [$prop1, $prop2, $arrProp1, $arrProp2, .key, .value] | @csv

    Demo

    And here’s another one without using variables by successively concatenating the final array:

    [.prop1, .prop2] + (.arr[] | [.arrProp1, .arrProp2] + (
  .valuePerDay | to_entries[] | [.key, .value]
)) | @csv

    Demo

    Login or Signup to reply.
  2. 0

    Open json format in Data.olllo Data Assistant, then save into csv format without any coding. You can download the software in the offical website. The free version is suitable for this senario.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search