2

In Postgres 9.5 I want to update a column with data from other columns in the same table. The issue is with "" signs because inside the JSON string it is not allowing to read data from actual column:

CREATE TABLE sample_table(
   id bigint
 , revision integer
 , var_data text
 , json_result jsonb
);

Sample data:

id  revision  var_data  json_result
1     10        test_A   {"EstimatedResult": 10, "RevOnly": 10}
2     20        test_B   {"EstimatedResult": 20, "RevOnly": 20}
3     30        test_C   {"EstimatedResult": 30, "RevOnly": 30}

Example 1: This is working: 

UPDATE "public"."sample_table"
SET "json_result" = "json_result" - 'EstimatedResult'
                  || jsonb_build_object('EstimatedResult', revision::numeric)::jsonb
WHERE json_result->>'EstimatedResult' IS NOT NULL;

Resulting json_result:

{"EstimatedResult": 10, "RevOnly": 10}
{"EstimatedResult": 20, "RevOnly": 20}
{"EstimatedResult": 30, "RevOnly": 30}

Example 2: THIS FAILED TO WORK:

The requirement is to modify the json structure to insert data from multiple columns. The result should be like this:

{"EstimatedResult": {"revid": 10, "displ": "test_A"}, "RevOnly": 10}
{"EstimatedResult": {"revid": 20, "displ": "test_B"}, "RevOnly": 20}
{"EstimatedResult": {"revid": 30, "displ": "test_C"}, "RevOnly": 30}

I know how to force data from colums in "RevOnly" using the Example1. However, I have no success forcing data from columns is "revid" and "displ"

I had success generating JSON value but the result was like this:

{"EstimatedResult": {"revid": "revision", "displ": "var_data"}, "RevOnly": 10}
{"EstimatedResult": {"revid": "revision", "displ": "var_data"}, "RevOnly": 20}
{"EstimatedResult": {"revid": "revision", "displ": "var_data"}, "RevOnly": 30}

Note "revision" and "var_data" are not updated.

How to update properly?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Dmitry
  • 165
  • 1
  • 9

2 Answers2

1

Assuming at least Postgres 9.5 and data type jsonb.
One elegant way is with jsonb_set() - combined with jsonb_build_object() you've already used successfully:

SELECT jsonb_set(json_result
               , '{EstimatedResult}'
               , jsonb_build_object('revid', revision, 'displ', var_data)
                )
FROM   sample_table;
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

This UPDATE command did exactly what I was looking for.
In case someone needs it.

UPDATE public.sample_table SET json_result =
       jsonb_set(json_result
               , '{EstimatedResult}'
               , jsonb_build_object('revid', revision::numeric, 'displ', var_data::text)) 
 WHERE json_result->>'EstimatedResult' IS NOT NULL
 RETURNING *;
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Dmitry
  • 165
  • 1
  • 9