sqlpostgresqljsonpathjson-search

How to get Postgres full JSON path from jsonb_path_query


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?


Solution

  • 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