Send key-value objects to postgres in queries

You essentially need to convert the JSON array you have into a set of rows that you can join to the product table:

Doing this is a bit complicated - at least I couldn't find a better way to do it:

with elements (name, map) as (
  select json_object_keys(map::json) as name, map::json
  from json_array_elements_text('[{"Gin" : "Au"}, {"Beer": "US"}]') as map
)
select name, trim(both '"' from (map -> name)::text) as location
from elements;

I have the feeling there should be a more elegant solution to turn the JSON into a relational set, but I can't figure it out right now.

This will return this:

name | location
-----+---------
Gin  | Au      
Beer | US      

Now this result can be used to update the product table:

with elements (name, map) as (
  select json_object_keys(map::json) as name, map::json
  from json_array_elements_text('[{"Gin" : "Au"}, {"Beer": "US"}]') as map
)
update product 
  set location = r.location
from (
  select name, trim(both '"' from (map -> name)::text) as location
  from elements
) as r (name, location)
  where product.name = r.name;

As you are going to do this a lot, I suggest to write a function that turns your JSON into a proper relational result:

create function get_products(p_mapping text)
  returns table (name text, location text)
as 
$$
  with elements (name, map) as (
    select json_object_keys(map::json) as name, map::json
    from json_array_elements_text(p_mapping::json) as map
  )
  select name, trim(both '"' from (map -> name)::text) as location
  from elements;
$$
language sql;

Then the update looks quite simple:

update product 
  set location = r.location
from get_products('[{"Gin" : "Au"}, {"Beer": "US"}]') r
  where product.name = r.name;