Postgres update jsonb fields

Question

I have a table "profile" with a jsonb field called "payment_methods". The jsonb field structure is this:

[{"name": "paypal", "primary": false}, 
 {"name": "braintree", "primary": true}, 
 {"name": "skrill", "primary": false}]

I want to make a query to dynamically set the primary payment method to the method that user will choose. If the user choose the paypal method as primary, i want to set the jsonb field like this:

[{"name": "paypal", "primary": true}, 
 {"name": "braintree", "primary": false}, 
 {"name": "skrill", "primary": false}]

So, i want to update the paypal's primary filed to true and any other payment method's primary field to false.

Notice: I want to filter with the name field. So if user give me for example paypal i want to set it as primary.

How can i do that?

J Spratt J Spratt · Accepted Answer · 2019-02-07T21:58:07

Not entirely sure of the structure of this table but I came up with this.

CREATE OR REPLACE FUNCTION func_updatePrimaryPaymentMethod(userid integer, pay_method_name text)
RETURNS SETOF profile
AS $$
DECLARE
    rec record;
BEGIN
    SELECT * INTO rec FROM profile WHERE id = userid;

    FOR i IN array_lower(rec.payment_methods, 1) .. array_upper(rec.payment_methods, 1)
    LOOP
        IF rec.payment_methods[i]->>'name' = pay_method_name
        THEN
            rec.payment_methods[i] := rec.payment_methods[i] || jsonb_build_object('primary', TRUE);
        ELSE
            rec.payment_methods[i] := rec.payment_methods[i] || jsonb_build_object('primary', FALSE);
        END IF;
    END LOOP;
    RETURN NEXT rec;
END;
$$  LANGUAGE PLPGSQL;

If this is a solution, I would recommend changing this design if you have the option to as this will always be hard to work with. You shouldn't have multiple jsonb objects stored in one field.

Why not just have the name and primary as their own columns?

Postgres update jsonb fields

1 Answers