skip to Main Content

Given this json:

{"data": [
  {
    "account_id": "123",
    "account_status": "active",
    "name": "john doe",
    "email": "[email protected]",
    "product_access": []
  },
  {
    "account_id": "345",
    "account_status": "active",
    "name": "jane doe",
    "email": "[email protected]",
    "last_active": "2023-08-23T13:03:27.811473590Z",
    "product_access": [
      {
        "name": "Product 1",
        "key": "product1",
        "url": "acme1.com"
      },
      {
        "name": "Product 1",
        "key": "product1",
        "url": "acme2.com"
      },
      {
        "name": "Product 1",
        "key": "product1",
        "url": "acme3.com"
      },
      {
        "name": "Product 2",
        "key": "product2",
        "url": "acme4.com",
        "last_active": "2023-08-23T13:03:27.811473590Z"
      },
      {
        "name": "Product 3",
        "key": "product3",
        "url": "acme5.com"
      },
      {
        "name": "Product 1",
        "key": "product1",
        "url": "acme4.com",
        "last_active": "2023-08-17T18:21:52.472085713Z"
      }
    ]
  }
]
}

I am trying to write a jq expression such that the output is the following in a csv file (not adding escape characters here for simplicity):

"account_id", "account_status", "name", "email", "Product 1", "Product 2", "Product 3"
"123", "active", "john doe", "[email protected]", ",,"
"345", "active", "jane doe", "[email protected]", "acme1.com, acme2.com, acme3.com, acme4.com", "acme4.com", "acme5.com"

I’ve tried this so far…

jq -r '["account_id", "account_status", "name", "email", "product 1", "product 2", "product 3"],
        (.data[] | [.account_id, .account_status, .name, .email,
                    (.product_access[] | select(.key == "product1").url // ""),
                    (.product_access[] | select(.key == "product2").url // ""),
                    (.product_access[] | select(.key == "product3").url // "")] | join(",")) | @csv'

It doesn’t collate the Product n urls into the single cells.

2

Answers


  1. You only want to apply the join to the products, not all the columns. You need to apply it to each product separately.

    jq -r '
    ["account_id", "account_status", "name", "email", "product 1", "product 2", "product 3"],
    (.data[]
        | [.account_id, .account_status, .name, .email,
          ([.product_access[] | select(.key == "product1").url] | join(",")),
          ([.product_access[] | select(.key == "product2").url] | join(",")),
          ([.product_access[] | select(.key == "product3").url] | join(","))
         ] ) | @csv'
    
    Login or Signup to reply.
  2. Try this :

    jq -r '
    ["account_id", "account_status", "name", "email", "product 1", "product 2", "product 3"],
    (.data[]
     | [.account_id, .account_status, .name, .email,
        ("product1","product2","product3") as $prod
        | [(.product_access[] | select(.key == $prod).url)] | join(",")
       ]
    )
    | @csv' input.json
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search