Jsonb operations with strapi

Questions and Answers Database-SQL
1

hey, Postgres allows really cool operations on the jsonb datatype, like accessing nested properties through queries etc. For example:

-- Find documents in which the key "company" has value "Magnafone"
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company": "Magnafone"}';

( more information is available here:
PostgreSQL: Documentation: 16: 8.14. JSON Types )

How would I go about this with strapi?

Would I use the raw method, or what would be the best practice here ?

let { rows } = await strapi.db.connection.raw(
    `SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company": "Magnafone"}`
);

Also I can’t find info on whether raw queries are auto-escaped or if they are prone to the usual injection risks ?

Thanks a lot ! :wave:

2

you could use knex Knex Query Builder | Knex.js
strapi.db.connection is knex. if that does not give you what you need you would have to fo raw.

1 Like