Unexpected column name when querying a json key in sqlite

[prutschman-iv]

I have some sample code below that creates an in-memory sqlite database with a single table containing a JSONField. I query it to extract 2 jsonkeys, and also prints the generated sql. The sql seems correct, but when I run the query with .dicts(), the resulting column name is strange, and only one of my query columns is present.

When I run it, I get:

SQL Query: ('SELECT "t1"."id", json_extract("t1"."json", ?), json_extract("t1"."json", ?) FROM "table" AS "t1"', ['$.value', '$.other_value'])
Results: [{'id': 1, 'json", ?': 3}]

I note two things:

• I only got one of my json query columns back,
• The column key is json", ?. I'm guessing that this is chosen for both columns, which is why I only get one back.

I can work around this with .alias, but it was unexpected.

I'm using peewee 3.17.7, python 3.11.9, on Windows.

from peewee import *
from playhouse.sqlite_ext import JSONField

db = SqliteDatabase(':memory:')

class Table(Model):
    id = IntegerField(primary_key=True)
    json = JSONField()

    class Meta:
        database = db

db.connect()
db.create_tables([Table])

record = Table.create(
    id=1,
    json={'value': 3, 'other_value': 4}
)

query = Table.select(Table.id, Table.json['value'], Table.json['other_value']).dicts()
print("SQL Query:", query.sql())
results = list(query)
print("Results:", results)

db.close()

[coleifer]

It's instructive to look at what Sqlite itself reports as the column names for your query:

query = Table.select(...)  # Your query.
curs = db.execute(query)
print([r[0] for r in curs.description])  # Print the column names as returned by Sqlite.

We get the following:

['id', 'json_extract("t1"."json", ?)', 'json_extract("t1"."json", ?)']

So the actual column name Sqlite gives us is json_extract("t1"."json", ?). Peewee attempts to clean these up, oftentimes it does a decent job, but it can't cover everything. That's why you end up with the weird dict keys. You need to use .alias() as you described.

Alternatively you can just use Table.select().tuples() and unpack the tuple into whatever variables you want to use.