How to concatenate strings to form a json object in Postgres?

Question

I have a string coldata->>'f1' which I need to send dynamically to fetch JSON object.

What I want exactly is:

RAISE NOTICE 'OBJECT %', fc_data->'firstname'->>'value';

In my case I am getting the firstname dynamically in the variable coldata->>'f1' in my stored procedure. I need to send the key dynamically to get the value. For which I tried the below code:

RAISE NOTICE 'OBJECT %', ((('fc_data->')::text) || '''' || ((coldata->>'f1')::text) || '''');

Which is displaying fc_data1->'firstname', this is being considered as string by Postgres and I want the value of it. So I tried converting it into jsonb as below:

RAISE NOTICE 'OBJECT %', ((('fc_data->')::text) || '''' || ((coldata->>'f1')::text) || '''')::jsonb;

It is giving me the below error:

ERROR:  invalid input syntax for type json
DETAIL:  Token "fc_data" is invalid.

How do I concatenate the string as key to the object?

@a_horse_with_no_name I have tried something and got a part of it. I just need to make text to json — Sai sri, Feb 19 '20 at 09:58

score 0 · Answer 1 · answered Feb 21 '20 at 04:01

The -> operator for json / jsonb takes text or int to the right, which can come from an expression. You can simply:

DO
$do$
DECLARE
   coldata jsonb := '{"f1":"firstname"}';
   fc_data jsonb := '{"firstname": {"value":123}}';
BEGIN
   RAISE EXCEPTION 'OBJECT %', fc_data -> (coldata->>'f1') ->> 'value';
END
$do$;

db<>fiddle here (I made it an exception in the fiddle, wo we get to see it.)

Just to add parentheses like demonstrated.

How to concatenate strings to form a json object in Postgres?

1 Answers1