How to extract data from a JSON file

Question

I have been searching for a solution for my question but didn't find a or better said I did not get it with what I found.  My problem is: I am using a Smart Home Control Software on a Raspberry Pi.  Using pilight-receive, I can capture the data from my outdoor temperature sensor.  The output of pilight-receive looks like that:

{
        "message": {
                "id": 4095,
                "temperature": 409.5
        },
        "origin": "receiver",
        "protocol": "alecto_wsd17",
        "uuid": "0000-b8-27-eb-0f3db7",
        "repeats": 3
}
{
        "message": {
                "id": 1490,
                "temperature": 25.1,
                "humidity": 40.0,
                "battery": 1
        },
        "origin": "receiver",
        "protocol": "alecto_ws1700",
        "uuid": "0000-b8-27-eb-0f3db7",
        "repeats": 3
}
{
        "message": {
                "id": 2039,
                "temperature": 409.5
        },
        "origin": "receiver",
        "protocol": "alecto_wsd17",
        "uuid": "0000-b8-27-eb-0f3db7",
        "repeats": 4
}

Now my question is: How the can I extract the temperature and humidity from messages where the id is 1490?  And how would you recommend me to do check this frequently?  By a cron job that runs every 10 minutes, creates an output of the pilight-receive, extracts the data of the output and pushes it to the Smart Home Control API?

Answer 1

You can use jq to process json files in shell.

For example, I saved your sample json file as raul.json and then ran:

$ jq .message.temperature raul.json 
409.5
25.1
409.5
$ jq .message.humidity raul.json 
null
40
null

jq is available pre-packaged for most linux distros.

There's probably a way to do it in jq itself, but the simplest way I found to get both the wanted values on one line is to use xargs. For example:

$ jq 'select(.message.id == 1490) | .message.temperature, .message.humidity' raul.json | xargs
25.1 40

or, if you want to loop through each .message.id instance, we can add .message.id to the output and use xargs -n 3 as we know that there will be three fields (id, temperature, humidity):

jq '.message.id, .message.temperature, .message.humidity' raul.json | xargs -n 3
4095 409.5 null
1490 25.1 40
2039 409.5 null

You could then post-process that output with awk or whatever.

---

Finally, both python and perl have excellent libraries for parsing and manipulating json data. As do several other languages, including php and java.

Answer 2

For those who don't understand advanced awk as well as they'd like to (such as people like me) and don't have jq pre-installed, an easy solution would be piping a couple of native commands together like so:

grep -A2 '"id": 1490,' stats.json | sed '/1490/d;s/"//g;s/,//;s/\s*//'

If you're only trying to get the values, it's easier just using grep rather than awk or sed:

grep -A2 '"id": 1490,' stats.json | grep -o "[0-9]*\.[0-9]*"

To provide an explanation, this seems like the simplest way to me.

• The grep -A2 grabs the line you are looking for in the JSON along with the following 2 lines, which contain the temperature and humidity.
• The pipe to grep -o simply prints only numerical digits separated by a . (which will never occur on the first 1490 line, so you are left with your 2 values -- temperature and humidity. Very simple. Even simpler than using jq, in my opinion.

Answer 3

My tool of choice for processing JSON on the command line is jq. However, if you don't have jq installed you can do pretty well with Perl:

# perl -MJSON -e '$/ = undef; my $data = <>; for my $hash (new JSON->incr_parse($data)) { my $msg = $hash->{message}; print "$msg->{temperature} $msg->{humidity}\n" if $msg->{id} == 1490 }' < data.json
25.1 40

Answer 4

jq is by far the most elegant solution. With awk you could write

awk -v id=1490 '
    $1 == "\"id\":" && $2 == id"," {matched = 1}
    $1 == "}," {matched = 0}
    matched && $1 ~ /temperature|humidity/ {sub(/,/,"", $2); print $2}
' file

Answer 5

your output is a set of JSON snippets rather than a complete JSON. If / once you rearrange your output to be an integral JSON, e.g. like this (assuming your output is in file.json):

echo "[ $(cat file.json | sed -E 's/^}$/},/; $d') }]"

then it's easy to achieve what you want with jtc tool (available at: https://github.com/ldn-softdev/jtc):

bash $ echo "[ $(cat file.json | sed -E 's/^}$/},/; $d') }]" | jtc -x "[id]:<1490>d [-1]" -y[temperature] -y[humidity] -l
"temperature": 25.1
"humidity": 40.0
bash $ 

in the example above drop -l if you don't want printed labels

Answer 6

To get the temperature and humidity from each message with id 1490, as a tab-delimited list, you may use

jq -r '.message | select(.id == 1490) | [ .temperature, .humidity ] | @tsv' 

Output given the data in the question:

25.1    40

To get CSV output, with the addition of a header, use

jq -s -r '[ "temperature", "humidity" ], (.[] | .message | select(.id == 1490) | [ .temperature, .humidity ]) | @csv' 

Note the added -s here to use jq in "slurp mode". It reads all the objects in the input set into a single array and we use this to first give the @csv operator the CSV header as an array, and then a set of arrays containing the individual CSV records that we have extracted from the data.

Output given the data in the question:

"temperature","humidity"
25.1,40