Parse JSONB/JSON columns when querying

[theelderbeever]

Is there a way to automatically parse JSONB/JSON data columns when performing a Table.select().run()? It seems like a useful feature instead of having to write the code to do it with every query.

[dantownsend]

@theelderbeever Yes, this should do the job:

await RecordingStudio.select().output(load_json=True)

[dantownsend]

I hadn't thought of it that way, but I don't understand why would someone run a raw query on a different table than the table the raw query was written for?

Yeah, I know it sounds weird, but the raw query could potentially do anything.

The asyncpg codecs (we can put it here) will only work if we explicit type cast the json column in query (so we go back to the original problem of detecting the json column)

Hmm, interesting. I still think that's an OK solution, but it would require quite a bit of refactoring.

We could somehow make it easy for the user to register their own asyncpg codecs, and then have that as a solution - that way we wouldn't have to do much refactoring.

[XCanG]

@dantownsend Sorry, I hadn't thought of it that way, but I don't understand why would someone run a raw query on a different table than the table the raw query was written for? The asyncpg codecs (we can put it here) will only work if we explicit type cast the json column in query (so we go back to the original problem of detecting the json column) like this

await Band.raw("SELECT id, name, facilities::json FROM studio")

Without that, the json column is not deserialized.

I can answer you that, because sometimes you just needed more data from other tables and so you join them in a way you ended up utilizing JSON/JSONB. for example I returning model of product that joined 5 topmost reviews, and so I have pydantic model like:

class ProductResponse(Product): # original model
    reviews: Sequence[Review]

and so it become JSONB field, e. g.

SELECT p.*, (
    SELECT jsonb_agg(r)
    FROM reviews r
    WHERE r.product_id = p.id
    ORDER BY r.likes DESC
    LIMIT 5
) AS "reviews"
FROM product p
WHERE p.id = {};

of course it's a simple example, but everything starts from there.

[sinisaos]

@XCanG Thanks for the example, but I think it has more to do with how you deserialize the raw query result than Piccolo. Converter like this works (at least in my case)

def jsonb_converter(data: dict[str, Any]) -> dict[str, Any]:
    for key, value in data.items():
        try:
            if isinstance(value, str) and json.loads(value):
                data[key] = json.loads(value)
        except ValueError:
            data[key] = value
    return data

That converter works with

@app.get("/")
async def index() -> JSONResponse:
    rows = await Band.raw("SELECT * FROM studio")
    return JSONResponse({"rows": [jsonb_converter(row) for row in rows]})

Also works with jsonb_agg

@app.get("/")
async def index() -> list[ProductResponse]:
    rows = await Product.raw(
        """
        SELECT p.*, (
            SELECT jsonb_agg(r)
            FROM review r
            WHERE r.product_id = p.id
        ) AS "reviews"
        FROM product p
        """
    )
    return [ProductResponse(**jsonb_converter(row)) for row in rows]

Result is:

[XCanG]

@sinisaos This solution is even more riskier as it add possibilities to inject object into arbitrary strings, consider you have text field called description: str and with your code user input something like:

>>> import json
>>> {"description": json.loads('"something"')}
{'description': 'something'}
>>> {"description": json.loads('["something"]')}
{'description': ['something']}
>>> {"description": json.loads('["something", false]')}
{'description': ['something', False]}
>>> {"description": json.loads('{"something": false}')}
{'description': {'something': False}}
>>> {"description": json.loads('0')}
{'description': 0}
>>> {"description": json.loads('Infinity')}
{'description': inf}
>>> {"description": json.loads('1e303')}
{'description': 1e+303}
>>> {"description": json.loads('null')}
{'description': None}

...
In some cases it will break models and produce uncertain errors.

And when it come to numbers, specifically BigInt's, it is known issue to parse them as numbers in other places, so large numbers typically sent as strings, with your code it will blindly convert them into integers and then frontend silently accept it but will parse wrongly.

JS example (considering it loading JSON from backend:

JSON.parse(`{"bigint": 123456789012345678901}`)
// result:
{bigint: 123456789012345680000}

[sinisaos]

@XCanG OK, then forget about my answers. Sorry.