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.

jjanes · Accepted Answer · 2021-07-20 01:35:33Z

4

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.

edited Jul 20, 2021 at 1:35

answered Jul 20, 2021 at 0:20

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

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

Collectives™ on Stack Overflow

How to get JSON key value using jsonpath

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