CODEWITHSUNDEEP
postgresql
TechArtificer
TechArtificer

Reputation: 97

Is there a way in Postgresql to query the entire JSONB record in a column?

I'm using Postgresql 13 / express.js with node-postgres for a test project. I have a table in Postgresql that contains a column where it holds an array of questions for an evaluation form (I made it as JSON as the questions can be edited to have more or lesser questions, dynamic form stuff).

With this idea, I made two tables: EvalForm and DefaultForm

EvalForm has id (PK, INT), job_id, form_type (INT), form_questions (JSONB), and form_answers (JSONB).

DefaultForm has id (PK, INT) and form_questions(JSONB).

The logic is that the form_type value will correspond to what form_questions will be copied based on its id. (e.g. form_type = 1, then it will copy the form_questions with id = 1 from DefaultForm table).

I would like to be able to copy the entire JSONB array and insert it into another table. I've read about jsonb_build_object and jsonb_agg(), but I'm not sure how to use them for my use-case.

Thank you in advance for the help!

This is currently the query that I've worked on, only missing on how to get JSONB as I can't cast type record to JSONB.

const type = await pool.query('SELECT job_type FROM jobs WHERE id = $1', [
  req.params.id
]);

 const sqlString = 'WITH questions AS (
 SELECT form_questions
FROM DefaultForm WHERE id = $2
 INSERT INTO EvalForm (
job_id, form_type, form_questions)
  SELECT $1, $2, questions::jsonb
FROM questions';

 const sqlValues = [req.params.id, type.rows[0].job_type
 ];

Upvotes: 0

Views: 102

Answers (1)

Bergi
Bergi

Reputation: 664277

jsonb_agg is the right choice, you're looking for

INSERT INTO EvalForm (job_id, form_type, form_questions)
SELECT $1, $2, jsonb_agg(form_questions)
FROM DefaultForm
WHERE id = $2;

Upvotes: 1

Related Questions