0

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"}]
Barmar
  • 741,623
  • 53
  • 500
  • 612
LeMoussel
  • 5,290
  • 12
  • 69
  • 122

0 Answers0