5

I'm trying to extract specific columns from this JSON file (which is probably incorrectly formatted).

Here is a data sample:

{ "_id" : { "$oid" : "4ddb1e4a9a0881572c000008" }, "aggregates" : { "_id" : { "$oid" : "4e0cc19ccd62f55ff9000006" }, "comments" : 0, "followers" : 1610, "following" : 92, "likes" : 8, "mig" : 1, "ntf" : 294, "posts" : 1, "yaf" : 1 }, "created_at" : { "$date" : 1306205770619 }, "devices" : [ "ios2:b54e0199cbcf73ba602bd11365b8bd3a7024796b", "ios2:7511337bea3c2378533fedceb156fae0a4a12bfe" ], "email" : "abc@example.edu", "email_verified" : true, "fb_info" : { "ext_user_id" : "123", "ext_access_token" : "tok_id", "ext_token_expr" : { "$date" : 1448577425000 }, "ext_username" : null, "fetched_at" : { "$date" : 1306205770610 }, "ext_permissions" : { "email" : 1, "offline_access" : 1, "contact_email" : 1, "user_friends" : 1, "publish_actions" : 1, "public_profile" : 1 }, "_id" : { "$oid" : "null" }, "ext_token_secret" : null }, "fb_user_id" : "123", "first_name" : "null", "gender" : "male", "last_name" : "null", "last_viewed_notification" : { "$oid" : "51525c4b4b807539ab002de4" }, "lns" : 1258, "profile" : { "city" : "San Francisco", "state" : "California", "_id" : { "$oid" : "4e6a9536cd62f5543b00000b" } }, "pv2" : { "desc" : "San Francisco, California" }, "reg_method" : "fb", "seg" : { "sup" : "I" }, "status" : "active", "username" : "null" }

There's multiple columns like that without any [] 3 lines in same format starting with { & end with }

So I have used jq in this example with adding commas between each field data. My command is:

cat example.json example.json example.json |
  jq -r '.status,.city,.gender' | paste -sd,

(The sample data is printed three times to reproduce the result I get using the real data).

The output was:

active,null,male,active,null,male,active,null,male

There should be a new line after each extracted data I don't now how to do that each time. I try to extract them I got all data in one line. The correct output should be with new line of each data.

active,null,male
active,null,male
active,null,male
5
  • Do you have three such lines in your input file? I'm afraid that is not quite clear.
    – Quasímodo
    Oct 3, 2020 at 14:21
  • 1
    I don't think this is how I would approach the issue (I'd probably try to make jq alone produce the desired output), but you seem to simply need paste -d, - - - (other things equal).
    – fra-san
    Oct 3, 2020 at 14:40
  • 1
    Using your example, I don't get the repeated output you suggest; I just get a single instance of active,null,male. Can you provide an input sample that actually maps on to desired multiple lines of output, and if not how are we supposed to "know" at what point the split is to occur?
    – roaima
    Oct 3, 2020 at 14:41
  • 4
    I'd use jq -r '[.status,.city,.gender]|@csv' Oct 3, 2020 at 14:54
  • I took the liberty to change your example command because it didn't look clear to other users. Please feel free to revert/improve it if needed. Also, must the output look literally as shown? E.g. can't it include double quotes? Does "null" need to be printed or could it be rendered as an empty string instead?
    – fra-san
    Oct 3, 2020 at 15:18

1 Answer 1

7

You can use the string interpolation feature of jq. You can output a string where the placeholders \() can contain selected values, and the rest is your surrounding text, all this inside double quotes. For your example:

jq -r '"\(.status),\(.city),\(.gender)"' example.json{,,}
active,null,male
active,null,male
active,null,male

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.