How do I select only a specific key's value from jsonb type in Postgres

Question

I have a jsonb column which has data as below.

[
   {"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 the value field from the array which has a key called slug, so that when I do a select query over table, I get this particular value from the column. For the above row when I do select name, slug, price from table, I should get med1, otc7087, 100 as the output. I am unable to build a query for this thing. I can get all the keys or all the values but how do I select a particular one in the same select query?

Or simply how do I select just the slugs from the table? That will answer.

Answer 1

i believe your json is much more structured , just try jsonb_to_recordset

for 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);

it will convert jsonb into table with key, value, display_name as columns and then you can fire any type query over it, it works for extracting keys also, whereas the way @Craig Ringer suggested you won't be able to convert it into table like things and firing complex select query like not in , != , range queries , ilike will be really difficult and might be less performant.