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.
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.