search

samuelcolvin / buildpg

Query building for the postgresql prepared statements and asyncpg.
MIT License
85 stars 12 forks source link

JSONB Examples? #48

Open CloudNiner opened 9 months ago

CloudNiner commented 9 months ago

Hi -- dug through the code, tests, and README and didn't see any JSONB examples.

We've effectively resorted to large RawDangerous() blocks which is a concern.

Any chance the README could provide at minimum a few examples for using this library to work with JSONB queries?

jp-u commented 3 months ago

JSONB examples would be really nice!

jp-u commented 3 months ago

I had some success using "JSONB Containment and Existence". This can be accomplished using the contains method, which maps to the @> (duck) operator, and was able to avoid using RawDangerous.

Suppose you have a JSONB column called parameters. You could build a filter using JSONB containment of another JSON document.

V("parameters").contains(json.dumps({"pokemon_type":"fire"}))

If instead pokemon_types is a list of strings, the following would match where pokemon_types includes both "fire" and "water".

V("parameters").contains(json.dumps({"pokemon_types":["fire", "water"]}))

If you wanted to match where pokemon_types includes either "fire" OR "water", you can match against a JSON document containing each one:

funcs.OR(
    V("parameters").contains(json.dumps({"pokemon_types":["fire"]})),
    V("parameters").contains(json.dumps({"pokemon_types":["water"]}))