What library do you use for postgres+jsonb in Node?

Question

I would like to do more complex queries on jsonb/documents that contain arrays of objects. Is there any library anyone would recommend for Node? I am using pg but I want to do more advanced queries like select the document where a document has an array with an object with a certain key/value. If there aren't any libraries that do this, does anyone know how I could do it with json functions/etc in psql? or point me to a book/resource where I could learn this advanced querying?

Answer 1

If you need to do really complicated things you're going to be writing SQL no matter what. But for basic queries that involve working with JSONB fields Massive (full disclosure, it's my project) has you covered, and executing handwritten prepared statements is as easy as anything else since scripts are loaded into the API.

Searching an embedded array falls into the 'really complicated' category, unfortunately, but if you know your element positions you could do this quite simply with Massive:

await db.mytable.find({
  'somejson.arrayfield[0].key': 'value'
});

This would return all records from mytable where the somejson column has an arrayfield array, the first element in which contains a "key": "value" pair.

For searching, check out the Postgres docs. The specific question you have requires a lateral join on the jsonb_array_elements function like so:

SELECT somejson
FROM mytable
JOIN LATERAL jsonb_array_elements(mytable.somejson->'arrayfield') AS elements
  ON TRUE
WHERE elements->>'key' = $1;

With Massive, you'd put this query in a script in your application's /db directory and run it as db.myScriptName('value'). You can use folders to group similar scripts too.