How to use json_each with alias() to query in SQLAlchemy/SQLite?

129 views Asked by At

I'm storing JSON documents in one of the columns of an SQLite table. The following query works fine if executed from the SQLite CLI

SELECT jsonfield.value
FROM dataset, json_each(dataset.samples) as jsonfield
WHERE json_extract(jsonfield.value, '$.instruction') == "intrus2"

Is it possible to write this query using SQLAlchemy/SQLite?

I try this

filter = 'intrus2'
query_samples = dbSession.query(Dataset)
   .select_from(Dataset, alias(func.json_each(Dataset.samples), 'jsonfield'))
   .filter(text('jsonfield.value ='\"' + filter + '\"'))

but I got error

alias() got an unexpected keyword argument 'flat'

Example Dataset.table

id (type INTEGER): 1    
samples (type JSON): [{"instruction": "intrus1", "input": "Twitter, Instagram, Telegram", "output": "T\u00e9l\u00e9gramme"}, {"instruction": "intrus2", "input": "Twitter, Instagram, Telegram", "output": "T\u00e9l\u00e9gramme"}]
0

There are 0 answers