Zeliax Zeliax - 4 years ago 147
Python Question

Extracting key-value pair from PostGreSQL jsonb column?

I have a postgresql database in which I have a column that contains a


I've come accross a situation where I need to extract a single key-value pair from my json object. I have currently no idea and have been unable to find any examples of how this is done.

I have the following table:

| id | did | timestamp | data | db_timestamp |

The data column is my json object and one examples of what it contains is:

{"n": 336372148490, "ac": 22.0, "al": 119.0, "be": 346.3, "la": 55.69707492, "lo": 12.58713834, "sa": 5, "sp": 2.6100767, "provider": "gps"}

So what I want to do is to write a query in which I look for a single key-value pair. I Currently have the following query,

SELECT data WHERE did = '357139052424715' and timestamp < 1466642640000

I want to extract a single key-value pair. How do I change the above query to extract
"la": 55.69707492

Answer Source

you can do this as:

SELECT data->>'la' WHERE did = '357139052424715' and timestamp < 1466642640000

For more examples see:


Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download