Generate a separate CSV record for each array element

Question

I have a JSON:

{
  "Country": "USA",
  "State": "TX",
  "Employees": [
    {
      "Name": "Name1",
      "address": "SomeAdress1"
    }
  ]
}
{
  "Country": "USA",
  "State": "FL",
  "Employees": [
    {
      "Name": "Name2",
      "address": "SomeAdress2"
    },
    {
      "Name": "Name3",
      "address": "SomeAdress3"
    }
  ]
}
{
  "Country": "USA",
  "State": "CA",
  "Employees": [
    {
      "Name": "Name4",
      "address": "SomeAdress4"
    }
  ]
}

I want to use jq to get the following result in csv format:

Country, State, Name, Address

USA, TX, Name1, SomeAdress1
USA, FL, Name2, SomeAdress2
USA, FL, Name3, SomeAdress3
USA, CA, Name4, SomeAdress4

I have got the following jq:

jq -r '.|[.Country,.State,(.Employees[]|.Name,.address)] | @csv'

And I get the following with 2nd line having more columns than required. I want these extra columns in a separate row:

"USA","TX","Name1","SomeAdress1"
"USA","FL","Name2","SomeAdress2","Name3","SomeAdress3"
"USA","CA","Name4","SomeAdress4"

And I want the following result:

"USA","TX","Name1","SomeAdress1"
"USA","FL","Name2","SomeAdress2"
"USA","FL","Name3","SomeAdress3"
"USA","CA","Name4","SomeAdress4"

@oguzismail I was expecting similar questions here, but wasn't able to find one. — Waqar Ahmed
– Waqar Ahmed, Commented Feb 3, 2021 at 11:18

oguz ismail · Accepted Answer · 2021-02-03 10:02:14Z

1

You need to generate a separate array for each employee.

[.Country, .State] + (.Employees[] | [.Name, .address]) | @csv

^{Online demo}

answered Feb 3, 2021 at 10:02

oguz ismail

51.9k16 gold badges64 silver badges83 bronze badges

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

1 Comment

Waqar Ahmed Over a year ago

I have accepted this answer as this works with my containerised jq. @andlr answer also works fine TBF, but in case if using a jq as a container in a build pipeline, it will require further tweaking.

Andreas Louv · Accepted Answer · 2021-02-03 09:59:44Z

1

You can store root object in a variable, and then expand the Employees arrays:

$ jq -r '. as $root | .Employees[]|[$root.Country, $root.State, .Name, .address] | @csv'
"USA","TX","Name1","SomeAdress1"
"USA","FL","Name2","SomeAdress2"
"USA","FL","Name3","SomeAdress3"
"USA","CA","Name4","SomeAdress4"

answered Feb 3, 2021 at 9:59

Andreas Louv

47.3k14 gold badges109 silver badges126 bronze badges

1 Comment

Waqar Ahmed Over a year ago

This answer works for me, but not in a pipeline where I am restricted to use a container for jq.

Weeble · Accepted Answer · 2021-02-04 00:25:02Z

The other answers are good, but I want to talk about why your attempt doesn't work, as well as why it seems like it should.

You are wondering why this:

jq -r '.|[.Country,.State,(.Employees[]|.Name,.address)] | @csv'

produces this:

"USA","TX","Name1","SomeAdress1"
"USA","FL","Name2","SomeAdress2","Name3","SomeAdress3"
"USA","CA","Name4","SomeAdress4"

perhaps because this:

jq '{Country:.Country,State:.State,Name:(.Employees[]|.Name)}'

produces this:

{
  "Country": "USA",
  "State": "TX",
  "Name": "Name1"
}
{
  "Country": "USA",
  "State": "FL",
  "Name": "Name2"
}
{
  "Country": "USA",
  "State": "FL",
  "Name": "Name3"
}
{
  "Country": "USA",
  "State": "CA",
  "Name": "Name4"
}

It turns out the difference is in what exactly [...] and {...} do in a jq filter. In the array constructor [...], the entire contents of the square brackets, commas and all, is a single filter, which is fully evaluated and all the results combined into one array. Each comma inside is simply the sequencing operator, which means generate all the values from the filter on its left, then all the values from the filter on its right. In contrast, the commas in the {...} object constructor are part of the syntax and just separate the fields of the object. If any of the field expressions yield multiple values then multiple whole objects are produced. If multiple field expressions yield multiple value then you get a whole object for every combination of yielded values.

When you do this:

jq -r '.|[.Country,.State,(.Employees[]|.Name,.address)] | @csv'
                  ^      ^                   ^
                  1      2                   3

the problem is that the commas labelled "1", "2" and "3" are all doing the same thing, evaluating all the values for the filter on the left, then all the values for the filter on the right. Then the array constructor catches all of them and produces a single array. The array constructor will never create more than one array for one input.

So with that in mind, you need to make sure that where you're expanding out .Employees[] isn't inside your array constructor. Here's another option to add to the answers you already have:

jq -r '.Employee=.Employees[]|[.Country,.State,.Employee.Name,.Employee.address]|@csv'

demo

or indeed:

jq -r '.Employees[] as $e|[.Country,.State,$e.Name,$e.address]|@csv'

demo

The manual explains all this in a single sentence under Array construction: All of the results produced by all of the expressions are collected into one big array.

Collectives™ on Stack Overflow

Generate a separate CSV record for each array element

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related