Postgres - jsonb : Update key in column with value taken from another table

Question

I am using postgres 9.5. I have a profile table, which lists the names:

public.profiles:
      id |   first_name      |        last_name
      --- ---------------  ---------------------
       1        Jason                   Bourne
       2       Jhonny                    Quest

I have an invoices table:

  public.invoices:
            invoice_id      |         billing_address       |    profile_id
         ------------------   -----------------------------  ---------------------
                   1          {                                        2
                               "address_line1": "445 Mount 
                                     Eden Road", 
                               "city":"Mount Eden", 
                               "country": "Auckland"
                              }

I want to update the billing_address column of the invoices table with the first_name and last_name from the profile table, like :

 public.invoices:
                invoice_id      |         billing_address       |    profile_id
             ------------------   -----------------------------  ---------------------
                       1          {
                                   "name" : "Jhonny Quest"                2
                                   "address_line1": "445 Mount 
                                         Eden Road", 
                                   "city":"Mount Eden", 
                                   "country": "Auckland"
                                  }

To do so, I have tried using jsonb_set:

UPDATE invoices AS i SET billing_address = jsonb_set(billing_address,'{name}', SELECT t::jsonb FROM (SELECT CONCAT (p.first_name,p.middle_name, p.last_name) FROM profiles p WHERE p.id = i.profile_id)t )

It throws an error at SELECT. TBH I am not even sure if any of that statement is legal. Looking for any guidance.

S-Man S-Man · Accepted Answer · 2020-01-08T13:47:06

Click: demo:db<>fiddle

UPDATE invoices i
SET billing_address = s.new_billing_address
FROM (
    SELECT
        i.invoice_id,
        jsonb_set(
            billing_address, 
            '{name}'::text[], 
            to_jsonb(concat_ws(' ', first_name, last_name))
        ) AS new_billing_address
    FROM 
        invoices i
    JOIN profiles p ON i.profile_id = p.id
) s
WHERE s.invoice_id = i.invoice_id;

Creating the SELECT with joining the second table; Afterwards you are able to create the new JSON object out of the name parts using to_jsonb() and the concat operator || (or concat_ws(), of course, as mentioned in the comments).

Postgres - jsonb : Update key in column with value taken from another table

1 Answers