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]);

Expecting

Answer 1

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.

Kimi

thanks @Kimi - I tried $1:json[] not $1::json[]. What is the difference when the double :: vs single : – Expecting

@Chev That's the library's formatting filter with : prefix, versus SQL type casting, which uses :: prefix. – Kimi