Using PostgreSQL JSON function to add a new key with object type value based on an existing attribut-CodePudding

I have a table on AWS RDS PostgreSQL that stores JSON objects. For instance I have this registry:

{
  "id": "87b05c62-4153-4341-9b58-e86bade25ffd",
  "title": "Just Ok",
  "rating": 2
}

And I need to add to the existing data a new key ratings of type object. If I run this query it gets added:

UPDATE public.reviews
SET data = jsonb_set(data, '{ratings}', '{ "quality": 4.0, "professionalism": 4.0, "flexibility": 3.0, "responseTime": 4.0, "value": 4.0 }')
WHERE data->>'id' = '87b05c62-4153-4341-9b58-e86bade25ffd';

But I need that the ratings values are added dynamically using the existing rate attribute. So far I have tried these two queries:

UPDATE public.reviews
SET data = jsonb_set(data, '{ratings}', ('{ "quality": ' || rating || ', "professionalism": ' || rating || ', "flexibility": ' || rating || ', "responseTime": ' || rating || ', "value": ' || rating || ' }')::json)
FROM (
    SELECT CAST(data->>'rating' AS DOUBLE PRECISION) AS rating
    FROM public.reviews
    WHERE data->>'id' = '87b05c62-4153-4341-9b58-e86bade25ffd'
) AS d
WHERE data->>'id' = '87b05c62-4153-4341-9b58-e86bade25ffd';

UPDATE public.reviews
SET data = jsonb_set(data, '{ratings}', ('{ "quality": ' || CAST(data->>'rating' AS DOUBLE PRECISION) || ', "professionalism": ' || CAST(data->>'rating' AS DOUBLE PRECISION) || ', "flexibility": ' || CAST(data->>'rating' AS DOUBLE PRECISION) || ', "responseTime": ' || CAST(data->>'rating' AS DOUBLE PRECISION) || ', "value": ' || CAST(data->>'rating' AS DOUBLE PRECISION) || ' }')::json)
WHERE data->>'id' = '87b05c62-4153-4341-9b58-e86bade25ffd';

On both cases I get the same error: ERROR: function jsonb_set(jsonb, unknown, json) does not exist

Any help would be greatly appreciated. Thanks in advance.

CodePudding user response：

No need to use jsonb_set if you want to add a new key.

You can use the concatenation operator ||

UPDATE public.reviews
  SET data = data||'{"ratings": { "quality": 4.0, "professionalism": 4.0, "flexibility": 3.0, "responseTime": 4.0, "value": 4.0 }}'
WHERE data->>'id' = '87b05c62-4153-4341-9b58-e86bade25ffd';

to include a value from the existing json, concatenate the strings, but make sure to enclose the whole string with parentheses:

SET data = data||('{"ratings": { "quality": '||(data ->> 'rating')||', "professionalism": 4.0, "flexibility": 3.0, "responseTime": 4.0, "value": 4.0 }}')::jsonb

Another option that might be less prone to errors is to use jsonb_build_object()

data = data||jsonb_build_object('ratings', 
                                jsonb_build_object('quality', data ->> 'rating',
                                                    'professionalism', 4.0, 
                                                    'flexibility', 3.0, 
                                                    'responseTime', 4.0, 
                                                    'value', 4.0))