PostgreSQL using COALESCE or other Conditional Expressions to SET field

Admin 1
Viewed 309

I have a PostgreSQL v10 DB with the following values:

CREATE TABLE test (
  id INT,
  custom_fields jsonb not null default '{}'::jsonb,
  guest_profile_id character varying(100)
 );
INSERT INTO test (id, custom_fields) VALUES (1, '[{"protelSurname": "Smith", "servicio_tags": ["protel-info"], "protelUniqueID": "[{\"ID\":\"Test1-ID\",\"Type\":\"21\",\"ID_Context\":\"GHA\"}{\"ID\":\"4842148\",\"Type\":\"1\",\"ID_Context\":\"protelIO\"}]",    "protelGivenName": "Seth"}, {"value": "Test", "display_name": "Traces", "servicio_tags": ["trace"]}, {...}]');
INSERT INTO test (id, custom_fields) VALUES (2, '[{"protelSurname": "Smith", "servicio_tags": ["protel-info"], "protelUniqueID": "[{\"ID\":\"Test2-ID\",\"Type\":\"21\",\"ID_Context\":\"GHA\"},{\"ID\":\"4842148\",\"Type\":\"1\",\"ID_Context\":\"protelIO\"}]", "protelGivenName": "Seth"}, {"value": "Test2", "display_name": "Traces", "servicio_tags": ["trace"]}, {...}]');
INSERT INTO test (id, custom_fields) VALUES (3, '[{"value": "Test3-ID", "display_name": "Test", "servicio_tags": ["profile-id"]}, {...}]');
INSERT INTO test (id, custom_fields) VALUES (4, '[{"value": "Test4-ID", "display_name": "Test", "servicio_tags": ["person-name"]}, {...}]');

I have a query, which works and saves values from the custom_field row to the guest_profile_id column in the same row:

UPDATE guest_group
SET guest_profile_id = (
SELECT x ->> 'ID'
FROM jsonb_array_elements(custom_fields) AS field,
     jsonb_array_elements((field ->> 'protelUniqueID') :: jsonb) AS dd(x)
WHERE value @> '{"servicio_tags": ["protel-info"]}'::jsonb
AND x->>'ID_Context' = 'protelIO' 
);

But this only works for the first two rows. Therefor I want to use the next query-snippets in order to copy Test3-ID in row 3 to the guest_profile_id column and Test4-ID in row 4 to the guest_profile_id column.

1.

SELECT field ->>'value'
cross join lateral jsonb_array_elements(custom_fields) AS field
WHERE value @> '{"servicio_tags": ["profile-id"]}'::jsonb

2.

SELECT field ->>'value'
cross join lateral jsonb_array_elements(custom_fields) AS field
WHERE value @> '{"servicio_tags": ["person-name"]}'::jsonb

My problem: I do not know how to use COALESCE or other Conditional Expressions in order to chain those small queries. Because this should be possible if the first query uses NULL to set the field, COALESCE should help me to ignore this value and jump to the next query-snippet.

Desires result: I want that all the TestX-ID values from the table above are copied to the guest_profile_id column into the same row.

My try: 

 UPDATE test
 SET guest_profile_id = COALESCE((
     SELECT x ->> 'ID'
     FROM jsonb_array_elements(custom_fields) AS field,
          jsonb_array_elements((field ->> 'protelUniqueID') :: jsonb) AS dd(x)
     WHERE value @> '{"servicio_tags": ["protel-info"]}'::jsonb
     AND x->>'ID_Context' = 'protelIO'),(
     SELECT field ->>'value'
     cross join lateral jsonb_array_elements(custom_fields) AS field
     WHERE value @> '{"servicio_tags": ["profile-id"]}'::jsonb),(
     SELECT field ->>'value'
     cross join lateral jsonb_array_elements(custom_fields) AS field
     WHERE value @> '{"servicio_tags": ["person-name"]}'::jsonb));

Gives me:

ERROR: syntax error at or near "cross" LINE 9: cross join lateral jsonb_array_elements(custom_fields) ...

Thanks a lot for the help!

postgresql
1 Answers

Some more brackets did the job around the select_queries:

guest_profile_id = COALESCE((first_select_query),((second_select_query)), ((…))) 

UPDATE test
SET guest_profile_id = COALESCE((
SELECT x ->> 'ID'
FROM jsonb_array_elements(custom_fields) AS field,
     jsonb_array_elements((field ->> 'protelUniqueID') :: jsonb) AS dd(x)
 WHERE value @> '{"servicio_tags": ["protel-info"]}'::jsonb
 AND x->>'ID_Context' = 'protelIO' LIMIT 1),((
 SELECT field ->>'value'
 FROM jsonb_array_elements(custom_fields) AS field
 WHERE value @> '{"servicio_tags": ["profile-id"]}'::jsonb LIMIT 1)), ((
 SELECT field ->>'value'
 FROM jsonb_array_elements(custom_fields) AS field
 WHERE value @> '{"servicio_tags": ["person-name"]}'::jsonb LIMIT 1
 )));

Here the link to a fiddle: Query which works

Admin1
Related