CODEWITHSUNDEEP
jsonperlshellcsvjq
user2711819
user2711819

Reputation: 960

How can I convert my JSON to CSV using jq?

I have the following JSON data:

{"id":"111","case":"Y","custom":{"speech invoked":"no","input method":"hard","session ID":"420"}}

How can I convert it to CSV format using jq so my result looks like this?

id,case,session Id,speech invoked,input method

111,Y,420,no,hard

I tried the following, but it didn't work:

{(.id),(.case),(.custom."session Id"),(.custom."speech invoked"),(.custom."input method")}

If not possible any perl or shell solution is appreciated.

Upvotes: 18

Views: 16450

Answers (5)

jq170727
jq170727

Reputation: 14695

Here is another solution. If data.json contains the sample data then

jq -M -s -r 'map(.+.custom|del(.custom)) | (.[0]|keys_unsorted), (.[]|[.[]]) | join(",")' data.json

will produce

id,case,speech invoked,input method,session ID
111,Y,no,hard,420

Upvotes: 1

Olivier Aubert
Olivier Aubert

Reputation: 335

Building upon Joe Harris' answer, you can use the @csv filter so that strings are properly quoted and escaped when necessary :

jq -r '[.case, .custom."speech invoked", .custom."input method"] | @csv'

Upvotes: 22

Jeff Mercado
Jeff Mercado

Reputation: 134511

Using jq, you can use this filter:

with_entries(select(.key != "custom")) + .custom
    | to_entries
    | map(.key), map(.value)
    | @csv

Just note that written this way, the "custom" properties will always be written in the end, no matter what order the properties are in.

Upvotes: 12

Joe Harris
Joe Harris

Reputation: 14035

Using perl wasn't a good solution for me but after a bit of trial and error I figured out you can do it with just jq using the join() operator.

First make an array of the output you need, then join the array elements using commas. 

jq -r '[.case, .custom."speech invoked", .custom."input method"] | join(", ")'

Enjoy. :)

Upvotes: 18

choroba
choroba

Reputation: 241988

Using Perl and its JSON module:

#!/usr/bin/perl
use warnings;
use strict;
use feature qw{ say };

use JSON;

my $input = << '__JSON__';
{"id":"111","case":"Y","custom":{"speech invoked":"no","input method":"hard","session ID":"420"}}
__JSON__

my $struct = decode_json($input);

my @header = grep ! ref $struct->{$_}, keys %$struct;
push @header, map keys %{ $struct->{$_} },
              grep ref $struct->{$_},
              keys %$struct;

my @row = grep ! ref, values %$struct;
push @row, map values %$_, grep ref, values %$struct;

say join ',', @header;
say join ',', @row;

Upvotes: 0

Related Questions