How to get JSON key value using jsonpath

Question

I'm new at jsonpath and want to query JSON fields in my PostgreSQL database by using jsonpath.

I use this query but it doesn't work.

SELECT jsonb person '$.years' FROM people

Person is a JSON field with the following value:

{
    "years": 2,
    "name": "Josh"
}

I use the documentation but it is not beginner-friendly.

How can I get a specific key's value from JSON using jsonpath? I use PostgreSQL 13.

Answer 1

Yes, the docs are certainly hard to learn from in this case. One problem is distinguishing the structure of the jsonpath language (described where you link to), from how the language is used inside the SQL language (described very tersely here). You need the SQL-language function named "jsonb_path_query" to apply the jsonpath query to the jsonb object.

SELECT jsonb_path_query( person::jsonb, '$.years') FROM people;

Note that the ::jsonb is only needed if "person" is not already defined to be datatype "jsonb". You would be better off making sure it already of the correct type, then you can omit the cast.