0

Input data.json

{
  "lastUpdateTime" : "2018-07-20T10:56:26.000Z",
  "items" : [ {
    "date" : "2018-07-19T21:09:27.000Z",
    "user" : "dddd",
    "size" : 5219402,
    "rawSize" : 15658206,
    "numFiles" : 119
  }, {
    "date" : "2018-07-19T21:09:27.000Z",
    "user" : "aaaa",
    "size" : 20524410845,
    "rawSize" : 61573215663,
    "numFiles" : 7540
  }, {
    "date" : "2018-07-19T21:09:27.000Z",
    "user" : "wwww",
    "size" : 0,
    "rawSize" : 0,
    "numFiles" : 2
  }, {
    "date" : "2018-07-19T21:09:27.000Z",
    "user" : "qqqq",
    "size" : 201084,
    "rawSize" : 603252,
    "numFiles" : 25
  }, {
    "date" : "2018-07-19T21:09:27.000Z",
    "user" : "ttttt",
    "size" : 280395332,
    "rawSize" : 288900666,
    "numFiles" : 199
  } ]
}

Expected output

User Size
aaa   121
bbb   123

How to do convert JSON to the above table? Please help me.

1
  • Welcome to Super User! What have you tried so far? What research have you done? :) – bertieb Jul 20 '18 at 12:07
1

Well, while I completely agree with @gronostaj about DON'T use awk or sed as parsing tool for JSON, I know sometimes can be cases where you can't use anything else except what is comes with OS.

If you absolutely sure that JSON you posted will be always in the same format as you posted, then solution is below:

#!/bin/sh

data='
{
  "lastUpdateTime" : "2018-07-20T10:56:26.000Z",
  "items" : [ {
    "date" : "2018-07-19T21:09:27.000Z",
    "user" : "dddd",
    "size" : 5219402,
    "rawSize" : 15658206,
    "numFiles" : 119
  }, {
    "date" : "2018-07-19T21:09:27.000Z",
    "user" : "aaaa",
    "size" : 20524410845,
    "rawSize" : 61573215663,
    "numFiles" : 7540
  }, {
    "date" : "2018-07-19T21:09:27.000Z",
    "user" : "wwww",
    "size" : 0,
    "rawSize" : 0,
    "numFiles" : 2
  }, {
    "date" : "2018-07-19T21:09:27.000Z",
    "user" : "qqqq",
    "size" : 201084,
    "rawSize" : 603252,
    "numFiles" : 25
  }, {
    "date" : "2018-07-19T21:09:27.000Z",
    "user" : "ttttt",
    "size" : 280395332,
    "rawSize" : 288900666,
    "numFiles" : 199
  } ]
}
'
###########################################################
echo "${data}" | awk -F: 'BEGIN{
  printf ("%s\t\t%s\t%s\n","Date", "User", "Size")
}
/lastUpdateTime/ {next}
/date/ { gsub(/\"|,|\s/,""); gsub(/T.+$/,""); printf ("%s\t", $2) }
/user/ { gsub(/\"|,|\s/,""); printf ("%s\t", $2) }
/size/ { gsub(/\"|,|\s/,""); printf ("%s\n", $2) }
'
2
  • Hi Alex, How to add date field like 2018-07-19 qqqq 201084 – Praveen Prakasan Jul 27 '18 at 15:03
  • I updated script, so it includes data field too... – Alex Jul 27 '18 at 17:15
4

The only honestly correct answer is:

Don't.

awk and sed aren't right tools for the job. You won't be able to properly deal with JSON escaping and encoding. You could try to cover some base cases, but you could as well just use a proper tool: jq.

jq solution

jq '.items[] | "\(.user) \(.size)"' -r /path/to/file

(alternatively, you can pipe JSON into the command instead of reading it from file)

To align columns:

jq '.items[] | "\(.user) \(.size)"' -r /path/to/file | column -t
1
  • +1 for "DON'T" do it with awk or sed, way to much things you can omit and overcomplicate. If you have PHP on the same machine, then it just a matter of single call to "json_decode()" – Alex Jul 20 '18 at 13:42

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.