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

Question

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.

score 1 · Accepted Answer · 2023-01-26 18:57:45Z

1

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))

edited Jan 26, 2023 at 18:57

answered Jan 26, 2023 at 18:51

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

JourneyToJsDude Over a year ago

thanks. the query i have used based on your solution and with casts because rating is string and ratings need decimal values is this: UPDATE public.reviews SET data = 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))|| ' }}')::jsonb WHERE data->>'id' = '87b05c62-4153-4341-9b58-e86bade25ffd';

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related