Extracting key-value pair from PostGreSQL jsonb column?

Question

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?

Mithilesh Gupta · Accepted Answer · 2017-02-06 09:12:33Z

2

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/

answered Feb 6, 2017 at 9:12

Mithilesh Gupta

2,9401 gold badge21 silver badges18 bronze badges

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

3 Comments

Zeliax Over a year ago

Can I do comparisons with this data as well? Such as SELECT data->>'la' WHERE did = '357139052424715' and timestamp < 1466642640000 and data->>'la' = <some-value>?

Mithilesh Gupta Over a year ago

Yes that is how you would filter

Zeliax Over a year ago

Thanks. This is exactly what I needed!

Collectives™ on Stack Overflow

Extracting key-value pair from PostGreSQL jsonb column?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related