PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – get specific element by key using sql query

i am storing JSON data as jsonb column and searching for specific object by key . Here is my json structure

  [
   {
    "item_tags": ["black", "optional"],
    "name": "Keyboard",
    "price": 50,
    "currency": "USD",
    "discount_price": 40
  },
 {
    "item_tags": ["white", "optional"],
    "name": "Mouse",
    "price": 40,
    "currency": "USD",
    "discount_price": 30
 }
]

sql query select r.data->1 from data_set gives me the correct result but i would like to get something like select r.data->@name='Mouse' from data_set Where name would be dynamic irrespective of position of the object inside array.

Tags:

3

Answers


  1. 0

    To achieve this in PostgreSQL with a dynamic search for an object within the JSON array based on the key name, you can use the jsonb_array_elements function, which will flatten the JSON array into individual objects. Then you can filter based on the name key within each object.

    You can try this by using the following SQL query:

    SELECT r.data
FROM data_set r,
LATERAL jsonb_array_elements(r.data) AS item
WHERE item->>'name' = 'Mouse';

    Explanation:

    • jsonb_array_elements(r.data) flattens the JSON array into individual JSON objects.
    • LATERAL allows you to use each element of the array as a row in the result set.
    • The ->> operator extracts the value of the name key as text from each JSON object.
    • The WHERE clause filters the results to only include objects where the name is ‘Mouse’.
    • This approach allows you to dynamically search for objects based on the value of the name key, regardless of the position of the object inside the array.
    Login or Signup to reply.
  2. 0

    I think you can just use jsonb_array_elements without LATERAL.

    SELECT elem
FROM test, jsonb_array_elements(test.data) AS elem
WHERE elem->>'name' = 'Mouse';

    Output

     {"name": "Mouse", "price": 40,
     "currency": "USD",
    "item_tags": ["white", "optional"], 
    "discount_price": 30}

    Example Fiddle

    Note : In the fiddle I tested with Lateral, but it returns both Keyboard and Mouse as name which is not desired.

    Login or Signup to reply.
  3. 0 
    select id, jsonb_path_query(r.data,'$[*]?(@.name=="Mouse")')
from data_set as r
where r.data @? '$.name=="Mouse"';

    The @? JSONPath operator is quick and flexible. It can speed up even more using a GIN index to get your results quickly and jsonb_path_query() unpacks each mouse into a separate row. It also makes no assumptions about the structure of the jsonb value.

    The .name accessor in the jsonpath expression doesn’t mind that it has to first open up an array element before it finds the name key in it.
    demo at db<>fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search