How to insert jsonb[] data into column using pg-promise

Question

Given a table with a column of type jsonb[], how do I insert a json array into the column?

Using the provided formatters :array, :json won't work in this instance - unless I am missing the correct combination or something.

const links = [
    {
        title: 'IMDB',
        url: 'https://www.imdb.com/title/tt0076759'
    },
    {
        title: 'Rotten Tomatoes',
        url: 'https://www.rottentomatoes.com/m/star_wars'
    }
];

 const result = await db.none(`INSERT INTO tests (links) VALUES ($1:json)`, [links]);

score 5 · Accepted Answer · edited Jun 20 '20 at 09:12

5

You do not need the library's :json filter in this case, as you need an array of JSON objects, and not a JSON with an array of JSON objects.

The former is formatted correctly by default, which then only needs ::json[] type casting:

    await db.none(`INSERT INTO tests(links) VALUES($1::json[])`, [links]);

Other Notes

Use pg-monitor or event query to output queries being executed, for easier diagnostics.
Method none can only resolve with null, no point storing the result in a variable.
Library pg-promise does not have any :array filter, see supported filters.

edited Jun 20 '20 at 09:12

Community

1
1

answered Nov 08 '19 at 09:05

vitaly-t

24,279
15
116
138

thanks @vitaly-t - I tried $1:json[] not $1::json[]. What is the difference when the double :: vs single : – Samuel Goldenbaum Nov 08 '19 at 17:44
@Chev That's the library's formatting filter with `:` prefix, versus SQL type casting, which uses `::` prefix. – vitaly-t Nov 09 '19 at 10:03

How to insert jsonb[] data into column using pg-promise

1 Answers1

Linked