CODEWITHSUNDEEP
sqlpostgresqljsonpathjson-search
Neil
Neil

Reputation: 3066

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?

Upvotes: 0

Views: 44

Answers (2)

Neil
Neil

Reputation: 3066

In my case, for anyone facing the same problem, my current solution would be

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
    ),
    found_match as (select ARRAY ['Laptop','specs',(ref.ord1 - 1)::text,'Brand'] as path
                     from json_data
                              join LATERAL jsonb_array_elements(json_data-> 'Laptop' -> 'specs' ) with ordinality as ref(v, ord1)
                                   on true and ref.v ->> 'name' = 'CPU')
update <table> set json_data=jsonb_set(<table>.<col>,fm.path, cast ('AMD' as jsonb))
from found_match fm where <table>.id=fm.id;

Upvotes: 0

samhita
samhita

Reputation: 3540

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

Upvotes: 2

Related Questions