CODEWITHSUNDEEP
postgresqljsonb
Catfish
Catfish

Reputation: 19294

How to insert JSONB data from dependent table

I'm trying to insert data into a JSONB field based on a dependent table.

Essentially I want to do this (ignore why this is just an example query):

insert into myschema.teams (team_name, params)
select users.team_name, '{"team_name": teams.team_id, "user_name": users.username }'
from myschema.users
where users.team_name is not null;

As written I'm getting these errors:

ERROR:  invalid input syntax for type json
LINE 2: ... '{"team_name...
            ^                                                 
DETAIL:  Token "teams" is invalid.
CONTEXT:  JSON data, line 1: {"team_name": teams...

Upvotes: 1

Views: 760

Answers (1)

Bergi
Bergi

Reputation: 664484

You are using a string literal that doesn't contain valid JSON. There is no interpolation going on - you need use the jsonb_build_object function to create the JSONB value from dynamic values. (You could also do string concatenation and the cast from text to json, but please don't).

insert into myschema.teams (team_name, params)
select users.team_name, jsonb_build_object('team_name', teams.team_name, 'user_name', users.username)
from myschema.users
where users.team_name is not null;

Upvotes: 1

Related Questions