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

jsonb
object.

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
only?

Answer Source

you can do this as:

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

For more examples see:

http://clarkdave.net/2013/06/what-can-you-do-with-postgresql-and-json/

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