structured query language for JSON (in Python)

Question

I am working on a system to output a JSON file and I use Python to parse the data and display it in a UI (PySide). I now would like to add filtering to that system and I think instead of writing a query system, if there was one out there for JSON (in Python), that would save me a lot of development time. I found this thread:

Is there a query language for JSON?

but that's more for a Web-based system. Any ideas on a Python equivalent?

EDIT [for clarity]:

The format the data that I'll be generating is like this:

{
    "Operations": [
    {
        "OpID": "0", 
        "type": "callback", 
        "stringTag1": "foo1", 
        "stringTag2": "FooMsg", 
        "Children": [...],
        "value": "0.000694053"
   },
   {
        "OpID": "1", 
        "type": "callback", 
        "stringTag1": "moo1", 
        "string2": "MooMsg", 
        "Children": [...],
        "value": "0.000468427"
   }
}

Where 'Children' could be nested arrays of the same thing (other operations). The system will be built to allow users to add their own tags as well to the data. My hope was to have a querying system that would allow users to define their own 'filters' as well, hence the question about the querying language. If there was something that would let me do something like "SELECT * WHERE "type" == "callback" and get the requisite operations back, that would be great.

The suggestion of Pync is interesting, I'll give that a look.

This seems like a fairly open-ended question that might need more research before it fits here. — Kyle Maxwell, Mar 09 '13 at 02:41
Can you give an example of the json you're looking at working with, as well as an expected query? — cwgem, Mar 09 '13 at 03:35

score 14 · Answer 1 · answered Mar 29 '19 at 21:14

I notice this question was asked a few years ago but if someone else find this, here are some newer projects trying to address this same problem:

ObjectPath (for Python and Javascript): http://objectpath.org/
jsonpath (Python reimplementation of the Javascript equivalent): https://pypi.org/project/jsonpath/
yaql: https://yaql.readthedocs.io/en/latest/readme.html
pyjq (Python bindings for jq https://stedolan.github.io/jq/): https://pypi.org/project/pyjq/
JMESPath: https://github.com/jmespath/jmespath.py

I personally went with pyjq because I use jq all the time for data exploration but ObjectPath seems very attractive and not limited to json.

score 11 · Accepted Answer · answered Mar 09 '13 at 03:57

I thought about this a little bit, and I lean towards something less specific such as a "JSON Query Language" and considered something more generic. I remembered from working with C# a bit that they had a somewhat generic querying system called LINQ for handling these sort of querying issues.

It looks as though Python has something similar called Pynq which supports basic querying such as:

filtered_collection = From(some_collection).where("item.property > 10").select_many()

It even appears to have some basic aggregation functions. While not being specific to JSON, I think it's a least a good starting point for querying.

the code hasn't been updated since 2009 and they pypi gave their package name away — Kermit, Aug 23 '22 at 16:32

score 3 · Answer 3 · answered Nov 06 '19 at 16:05

3

You can also check out PythonQL, a query language extension to Python that handles SQL and JSON queries: pythonql

answered Nov 06 '19 at 16:05

randomsurfer_123

315
2
15

Kermit · Answer 4 · 2022-08-23T13:55:55.223

0

pyjsonq

https://github.com/s1s1ty/py-jsonq

from pyjsonq import JsonQ

qe = JsonQ('myfile.json')
res = qe.at('products').where('cat', '=', 2).get()
print(res)

"""
[
    {
        id: 3,
        city: 'dhk',
        name: 'Redmi 3S Prime',
        cat: 2,
        price: 12000
    },
    ...
]

I think it's important that the interaction with json is in-memory so that you can still do things manually for complex criteria

edited Aug 23 '22 at 13:55

answered Aug 23 '22 at 13:44

Kermit

4,922
4
42
74

UPDATE: i'm having trouble accessing nested structures using this library – Kermit Aug 23 '22 at 16:13

structured query language for JSON (in Python)

4 Answers4

Linked