Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

structured query language for JSON (in Python)

Tags:

python

json

sql

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.

like image 680
easythrees Avatar asked Mar 09 '13 02:03

easythrees


People also ask

How do I query JSON data in Python?

Example-1: Search key in simple JSON data Here, a variable named customerData is defined to store the JSON data. The value of the key will be taken as input from the user. loads() method of JSON module is used to load JSON data in the variable named customer. Next, 'in' operator is used to search the key.

Is there a query language for JSON?

JAQL is a query language for the JavaScript Object Notation (JSON) data interchange format. Pronounced "jackal," JAQL is a functional, declarative programming language designed especially for working with large volumes of structured, semi-structured and unstructured data.

What is the best way to work with JSON in Python?

Start by importing the json library. We use the function open to read the JSON file and then the method json. load() to parse the JSON string into a Python dictionary called superHeroSquad. That's it!

How do I query JSON data?

To query JSON data, you can use standard T-SQL. If you must create a query or report on JSON data, you can easily convert JSON data to rows and columns by calling the OPENJSON rowset function. For more information, see Convert JSON Data to Rows and Columns with OPENJSON (SQL Server).


2 Answers

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.

like image 63
cwgem Avatar answered Sep 30 '22 12:09

cwgem


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.

like image 30
Josep Valls Avatar answered Sep 30 '22 13:09

Josep Valls