Query of Postgresql JSON column containing array of objects returns nulls

Question

I have Postgresql table named heart_rate_json with a JSON column named data containing an array of objects like this:

[{
  "dateTime" : "01/24/15 21:07:45",
  "value" : {
    "bpm" : 70,
    "confidence" : 0
  }
},{
  "dateTime" : "01/24/15 21:08:30",
  "value" : {
    "bpm" : 64,
    "confidence" : 2
  }
 }]

When I run this query hoping to extract array object values:

SELECT 
data -> 'dateTime' AS datetime,
data -> 'value' ->> 'bpm' AS bpm,
data -> 'value' ->> 'confidence' AS confidence 
FROM heart_rate_json;

The query result has three columns but all values are [null].

Selecting the data column does return results containing the entire array of objects:

SELECT data FROM heart_rate_json;

What am I missing here?

Answer 1

You need first expands a JSON array to a set of JSON elements (you can use: json_array_elements(json)):

SELECT 
      data -> 'dateTime' AS datetime,
      data -> 'value' ->> 'bpm' AS bpm,
      data -> 'value' ->> 'confidence' AS confidence 
FROM (SELECT json_array_elements(data) AS data FROM heart_rate_json) AS sq;