pudo / dataset

Easy-to-use data handling for SQL data stores with support for implicit table creation, bulk loading, and transactions.
https://dataset.readthedocs.org/
MIT License
4.76k stars 297 forks source link

Add selecting json function into find method #379

Open Remalloc opened 3 years ago

Remalloc commented 3 years ago

Related #262. Hi pudo, I have implemented selecting json function that can combine with other find parameters. Here are some using examples:

# Notice: selected key type depends on giving value type,
#         like if given integer but stored type is float will be automatically transformed to integer.
# Support operations: >(gt), <(lt), >=(gte), <=(lte), =(==,is), !=(<>, not), between("..")
# id json_column
# 0  {"key":-0.5}
# 1  {"key":0.5}
# 2  {"key":1.5}
results = table.find(_json={'json_column':{'key':{'>=': 0.0, '<':1.0}}}) # id = [1]
results = table.find(_json={'json_column':{'key':{'>=': 0, '<':1}}}) # int(-0.5)==0, id = [0,1]

# id json_column
# 0  [0,1,2]
# 1  [0,0.5,1]
# 2  [0]
# find rows by index
results = table.find(_json={'json_column':{1:{'>=': 0.0, '<':1.0}}}) # id = [1]

# id json_column
# 0  {"key1":{"key2":-1}}
# 1  {"key1":{"key2":0.5}}
# find rows by path
results = table.find(_json={'json_column':{('key1','key2'):{'between':[0.0,1.0]}}}) # id = [1]

However, current sqlite standard library's version does not support this function. So, I have no idea how to add unittest.