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.

Monstrance

This is a weird way to store data. It's like you're doing EAV with json. Why don't you store objects? {"unit_type": {"value": "Tablet", "display_name": "Unit Type"}, ...} or something? – Dealate

Also, where does med1 and 100 come from? Some data not shown here? – Dealate

sorry, I forgot to mention. These are some other columns. I want just the slug from the above column and these two or others will be in select statement. – Monstrance

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.

Property

Answer 2

You seem to want to search all elements of a json array for an object with a particular value for a given key, then return the value of another key if matched.

Something like this will do the trick:

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';

i.e. select from the table, unpack the array into a join, filter the join table for the desired object by looking for the json key key with value slug, and return the value of the json key value in the select clause when found.

If you want multiple different values from the same json object you need multiple joins, one per desired value.

This is a pretty ugly way to store variable key/value format data. I'd suggest storage like:

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

where you can actually look up the keys.

Dealate

Answer 3

For others coming to this years after the question, there was a function called "jsonb_path_query" added to Postgres in v12 which makes this easier. I think the following is what's needed:

select jsonb_path_query(<column-with-json>, '$ ? (@.key == "slug")')->>'value' from <table>

Sweeten