CODEWITHSUNDEEP
sqlpostgresql
Bravo
Bravo

Reputation: 1139

Send key-value objects to postgres in queries

Assume I have the following key-value object:

[{"Gin" : "Au"}, {"Beer": "US"}]

Now, I would like to perform the following queries:

UPDATE product SET location = "AU" WHERE name = "Gin";

UPDATE product SET location = "Beer" WHERE name = "US";

Is it possible to send a key-value object to postgres so it will do the mapping on its own? I want to wrap everything in one request to the database.

Upvotes: 0

Views: 807

Answers (1)

user330315
user330315

Reputation:

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;

Upvotes: 1

Related Questions