I need to update jsonb column by jsonb_set, to use jsonb_set, need a full Json Path. How could I get full jsonb path when jsonb has list?
For example, I got a JSON :
{
"Laptop": {
"brand": "Dell",
"price": 1200,
"specs": [
{
"name": "CPU",
"Brand": "Intel"
},
{
"name": "GPU",
"Brand": "Nvdia"
},
{
"name": "RAM",
"Brand": "Kingston"
}
]
}
}
I need some query like
SELECT <full_path>
FROM table
WHERE jsonb_path_query_first(col,"$.Laptop.specs[*].name")::text='"CPU"'`;
I need a full path that matches the query above the return could be
$.Laptop.specs[0].name
or even better {Laptop,specs,0,name}
Is there any way to achieve this in Postgres?
Based on my understanding you need the index of the path? let me know
WITH json_data AS (
SELECT '{"Laptop": {"brand": "Dell", "price": 1200, "specs": [{"name": "CPU", "Brand": "Intel"}, {"name": "GPU", "Brand": "Nvdia"}, {"name": "RAM", "Brand": "Kingston"}]}}'::jsonb AS col
)
SELECT
CONCAT('$.Laptop.specs[', idx - 1, '].name') AS full_json_path
FROM
json_data,
LATERAL jsonb_array_elements(col->'Laptop'->'specs') WITH ORDINALITY arr(elem, idx)
WHERE
elem->>'name' = 'CPU';
Output
$.Laptop.specs[0].name