Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to get value from JSON with dynamic key in Postgres

The following query gets all key names that are needed from a jsonb object. Basically, getting all key names that contain 'foo' from a column called 'objects'.

SELECT keys
FROM (
    SELECT jsonb_object_keys(objects) as keys
    from table
) as testvalues
WHERE keys LIKE '%foo%';

Once I get the set of key names, I want to access the status field of that object as follows

SELECT objects->'hello_foo'->'status' FROM table

However, I still can't find a way to go through the set of key names in the first query and dynamically access the status field.

What would be the best implementation?

like image 982
chris Avatar asked Oct 16 '25 18:10

chris


2 Answers

You can use jsonb_each() to extract all elements as key/value pairs. Then you can filter by the keys and access the value objects:

demo:db<>fiddle

SELECT
    *,
    objects.value -> 'status'
FROM
    t,
    jsonb_each(mydata) objects
WHERE objects.key::text LIKE '%foo%'
like image 99
S-Man Avatar answered Oct 18 '25 07:10

S-Man


You can use funciton jsonb_extract_path.

For example, if you have table my_json_data, whihc has fields row_id and json_data, then you can use query below:

SELECT jsonb_extract_path(json_data, 'pathName') AS my_value
FROM my_json_data WHERE row_id = 100;

You can replace 'pathName' with variable etc.

like image 20
D.A.H Avatar answered Oct 18 '25 09:10

D.A.H