How to select only a specific key value from jsonb type in Postgres

Question

I have a column jsonbthat has the following data.

[
   {"key": "unit_type", "value": "Tablet", "display_name": "Unit Type"},
   {"key": "pack_type", "value": "Packet", "display_name": "Pack Type"},
   {"key": "units_in_pack", "value": "60", "display_name": "Units in Pack"},
   {"key": "item_unit", "value": "", "display_name": "Item unit"},
   {"key": "item_size", "value": "1", "display_name": "Item Size"}, 
   {"key": "details", "value": "", "display_name": "Details"},
   {"key": "slug", "value": "otc7087", "display_name": "Slug"}
]

I want to get a field valuefrom an array with keycalled slug, so when I make a select query on a table, I get this specific value from a column. For the above line, when I do select name, slug, price from table, I should get med1, otc7087, 100as output. I cannot build a query for this thing. I can get all keys or all values, but how to select a specific one in the same select query?

Or just how to choose only slugsfrom the table? That will answer.

Answer 1

, json , jsonb_to_recordset

ex:

select * from json_to_recordset('[
   {"key": "unit_type", "value": "Tablet", "display_name": "Unit Type"},
   {"key": "pack_type", "value": "Packet", "display_name": "Pack Type"},
   {"key": "units_in_pack", "value": "60", "display_name": "Units in Pack"},
   {"key": "item_unit", "value": "", "display_name": "Item unit"},
   {"key": "item_size", "value": "1", "display_name": "Item Size"}, 
   {"key": "details", "value": "", "display_name": "Details"},
   {"key": "slug", "value": "otc7087", "display_name": "Slug"}
]') as x(key int, value text, display_name text);

jsonb , , display_name , , , @Craig Ringer , , , , ,! =, , ilike .

Answer 2

, json , , .

- :

WITH my_table(jsonblob) AS (VALUES('[
   {"key": "unit_type", "value": "Tablet", "display_name": "Unit Type"},
   {"key": "pack_type", "value": "Packet", "display_name": "Pack Type"},
   {"key": "units_in_pack", "value": "60", "display_name": "Units in Pack"},
   {"key": "item_unit", "value": "", "display_name": "Item unit"},
   {"key": "item_size", "value": "1", "display_name": "Item Size"}, 
   {"key": "details", "value": "", "display_name": "Details"},
   {"key": "slug", "value": "otc7087", "display_name": "Slug"}
]'::jsonb))
SELECT elem ->> 'value'
FROM my_table
CROSS JOIN LATERAL jsonb_array_elements(jsonblob) elem
WHERE (elem ->> 'key') = 'slug';

. , , , json key key slug json key value select .

json, , .

/. , :

{"unit_type": {"value": "Tablet", "display_name": "Unit Type"}, ...}

.