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?

nachospiu · Accepted Answer · 2021-07-19 21:28:50Z

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;

answered Jul 19, 2021 at 21:28

nachospiu

2,0792 gold badges10 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Query of Postgresql JSON column containing array of objects returns nulls

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related