JQ filter formatting

Question

I have a JSON file I'd like to filter by the ID field, and show the matching Body and Source fields.

Format of the JSON file to filter

[
{
   "timestamp" : 1638550971085,
   "id" : "54f",
   "body" : "Orange",
   "source" : "827261"
},
{
   "timestamp" : 1638550971096,
   "id" : "54f",
   "body" : "Apple",
   "source" : "137261"
},
{
   "timestamp" : 1638550971126,
   "id" : "5da",
   "body" : "Pear",
   "source" : "1da61"
}
]

In this example I would like to filter where id = 54f and show the Timestamp (Unixtime converted to local time), Body and Source fields that match, ideally as shown below.

[Timestamp]    Orange     827261
[Timestamp]    Apple      137261

I have tried this command, but it is showing extra body / source fields outside of the SQL filter. It also adds a line break between printing the body and source, and ideally I'd like this printed on one line (tab separated). I also don't know how to convert the timestamp to localtime string.

more file.json | jq '.[] | select(.Id=="54f").body, .source'

pmf · Answer 1 · 2022-08-29T16:32:20.870

Your JSON input is not proper JSON as it has

commas after the .source field but no following field
no commas between the elements of the top-level array
no quotes around the objects' field names

You'd need to address these issues first before proceeding. This is how it should look like:

[
  {
    "timestamp": 1638550971085,
    "id": "54f",
    "body": "Orange",
    "source": "827261"
  },
  {
    "timestamp": 1638550971096,
    "id": "54f",
    "body": "Apple",
    "source": "137261"
  },
  {
    "timestamp": 1638550971126,
    "id": "5da",
    "body": "Pear",
    "source": "1da61"
  }
]

Then you can go with this

localtime (available since jq 1.6) converts a timestamp of seconds (so, divide yours by 1000) since the Unix epoch into a so-called "broken down time" object (see the manual) which you can either process using strftime (see the answer from David Conrad), or parse yourself manually. With .[:3] | .[1] += 1 | join("-") I provided a rather primitive example for demonstration purposes which concatenates its first three items (year, month, day) with dashes in between, after incrementing the second item (as the month has a 0-based encoding) - for padding with zeroes check out one of the answers over here
@tsv creates tabs between the columns

jq -r '
  .[]
  | select(.id == "54f")
  | [(.timestamp / 1000 | localtime | .[:3] | join("-")), .body, .source]
  | @tsv
' file.json

2021-12-3   Orange  827261
2021-12-3   Apple   137261

Demo

The month in the "broken down time" goes from 0-11, not 1-12, so you are displaying the wrong month. There is an easy way to fix that *and* to display leading zeroes without any left pad code. Use `| localtime | strftime("%Y-%m-%d")`. — David Conrad, Aug 29 '22 at 13:47
@DavidConrad Thank you for pointing out these issues. I have fixed the month issue and referenced your solution for the approach with `strftime`. — pmf, Aug 29 '22 at 16:32

score 0 · Answer 2 · answered Aug 29 '22 at 13:53

As the other answer states, your JSON is not correct. After fixing that, you can filter and extract the data as that answer suggests, but use the strftime function to format the dates properly:

jq -r '.[] | select(.id == "54f")
    | [(.timestamp / 1000 | localtime | strftime("%Y-%m-%d")), .body, .source]
    | @tsv' file.json

The use of strftime("%Y-%m-%d") is critical to both displaying the correct month and formatting the date with leading zeroes on single-digit months and days.

2021-12-03      Orange  827261
2021-12-03      Apple   137261

JQ filter formatting

2 Answers2