The fastest tool for querying large JSON files is written in Python (benchmark)

1. simdjson

   1 71 20,382 8.8 C++

   Parsing gigabytes of JSON per second : used by Facebook/Meta Velox, the Node.js runtime, ClickHouse, WatermelonDB, Apache Doris, Milvus, StarRocks

   

   Daniel Lemire’s simdjson probably belongs in this discussion and I would be surprised if it is not the fastest tool by some margin.

   https://github.com/simdjson/simdjson

2. InfluxDB

   www.influxdata.com featured

   InfluxDB – Built for High-Performance Time Series Workloads. InfluxDB 3 OSS is now GA. Transform, enrich, and act on time series data directly in the database. Automate critical tasks and eliminate the need to move data externally. Download now.

   

3. ojg

   2 17 887 6.8 Go

   Optimized JSON for Go

   

   For me OjG (https://github.com/ohler55/ojg) has been great. I regularly use it on files that can not be loaded into memory. The best JSON file format for multiple record is one JSON document per record all in the same file. OjG doesn't care if they are on different lines. It is fast (https://github.com/ohler55/compare-go-json) and uses a fairly complete JSONPath implementation for searches. Similar to jq but using JSONPath instead of a proprietary query language.

   I am biased though as I wrote OjG to handle what other tools were not able to do.

4. compare-go-json

   3 5 22 0.0 Go

   A comparison of several go JSON packages.

   

   For me OjG (https://github.com/ohler55/ojg) has been great. I regularly use it on files that can not be loaded into memory. The best JSON file format for multiple record is one JSON document per record all in the same file. OjG doesn't care if they are on different lines. It is fast (https://github.com/ohler55/compare-go-json) and uses a fairly complete JSONPath implementation for searches. Similar to jq but using JSONPath instead of a proprietary query language.

   I am biased though as I wrote OjG to handle what other tools were not able to do.

5. ultrajson

   4 5 4,404 7.5 C

   Ultra fast JSON decoder and encoder written in C with Python bindings

   

   I asked about this on the Github issue regarding these benchmarks as well.

   I'm curious as to why libraries like ultrajson[0] and orjson[1] weren't explored. They aren't command line tools, but neither is pandas right? Is it perhaps because the code required to implement the challenges is large enough that they are considered too inconvenient to use through the same way pandas was used (ie, `python -c "..."`)?

   [0] https://github.com/ultrajson/ultrajson

6. orjson

   5 22 6,923 8.2 Python

   Fast, correct Python JSON library supporting dataclasses, datetimes, and numpy

   

7. bert

   6 55 39,124 0.0 Python

   TensorFlow code and pre-trained models for BERT

   

   > resulting in large programs with lots of boilerplate

   That was what I was trying to say when I said "the code required to implement the challenges is large enough that they are considered too inconvenient to use". This makes sense to me.

   Thank you for this benchmark! I'll probably switch to spyql now from jq.

   > So, orjson is part of the reason why a python-based tool outperforms tools written in C, Go, etc and deserves credit.

   Yes, I definitely think this is worth mentioning upfront in the future, since, IIUC, orison's core uses Rust (the serde library, specifically). The initial title gave me the impression that a pure-Python json parsing-and-querying solution was the fastest out there, which I find misleading.

   A parallel I think is helpful is saying something like "the fastest BERT implementation is written Python[0]". While the linked implementation is written in Python, it offloads the performance critical parts to C/C++ through TensorFlow.

   [0] https://github.com/google-research/bert

8. catj

   7 3 1,330 0.0

   Displays JSON files in a flat format.

   

   My main problem with jq was finding the paths to the nodes, so I wrote catj (https://github.com/soheilpro/catj) to help with that.

9. SaaSHub

   www.saashub.com featured

   SaaSHub - Software Alternatives and Reviews. SaaSHub helps you find the best software and product alternatives

   

10. db-benchmark

    8 92 332 0.0 R

    reproducible benchmark of database-like ops

    

11. pysimdjson

    9 6 691 0.0 Python

    Python bindings for the simdjson project.

    

    json: 113.79130696877837 ms

    While `orjson`, is faster than `ujson`/`json` here, it's only ~6% faster (in this benchmark). `simdjson` and `msgspec` (my library, see https://jcristharif.com/msgspec/) are much faster due to them avoiding creating PyObjects for fields that are never used.

    If spyql's query engine can determine the fields it will access statically before processing, you might find using `msgspec` for JSON gives a nice speedup (it'll also type check the JSON if you know the type of each field). If this information isn't known though, you may find using `pysimdjson` (https://pysimdjson.tkte.ch/) gives an easy speed boost, as it should be more of a drop-in for `orjson`.

12. msgspec

    10 32 2,840 6.9 Python

    A fast serialization and validation library, with builtin support for JSON, MessagePack, YAML, and TOML

    

    json: 113.79130696877837 ms

    While `orjson`, is faster than `ujson`/`json` here, it's only ~6% faster (in this benchmark). `simdjson` and `msgspec` (my library, see https://jcristharif.com/msgspec/) are much faster due to them avoiding creating PyObjects for fields that are never used.

    If spyql's query engine can determine the fields it will access statically before processing, you might find using `msgspec` for JSON gives a nice speedup (it'll also type check the JSON if you know the type of each field). If this information isn't known though, you may find using `pysimdjson` (https://pysimdjson.tkte.ch/) gives an easy speed boost, as it should be more of a drop-in for `orjson`.

13. datasette

    11 198 10,020 8.5 Python

    An open source multi-tool for exploring and publishing data

    

    "Datasette" (from Django co-creator) can take tabular data (SQLite, CSV, JSON, etc) and generate a REST/GraphQL API with visualization tools from it:

    https://github.com/simonw/datasette

    From the same author, "sqlite-utils" can take tabular data and create SQLite table definitions and rows from them:

    https://github.com/simonw/sqlite-utils

    "Pipe JSON (or CSV or TSV) directly into a new SQLite database file, automatically creating a table with the appropriate schema"

      > * What sort of JSON "meta-formats" are the most important/common for you? E.g. in a file you could have object-per-line, object-of-arrays, array-of-objects, or in an SQL context you could have object-per-row or object-of-arrays-as-table, etc). I'd love to hear about others that are important to you.

14. sqlite-utils

    12 38 1,818 7.1 Python

    Python CLI utility and library for manipulating SQLite databases

    

    "Datasette" (from Django co-creator) can take tabular data (SQLite, CSV, JSON, etc) and generate a REST/GraphQL API with visualization tools from it:

    https://github.com/simonw/datasette

    From the same author, "sqlite-utils" can take tabular data and create SQLite table definitions and rows from them:

    https://github.com/simonw/sqlite-utils

    "Pipe JSON (or CSV or TSV) directly into a new SQLite database file, automatically creating a table with the appropriate schema"

      > * What sort of JSON "meta-formats" are the most important/common for you? E.g. in a file you could have object-per-line, object-of-arrays, array-of-objects, or in an SQL context you could have object-per-row or object-of-arrays-as-table, etc). I'd love to hear about others that are important to you.

15. ojc

    13 1 34 2.3 C

    Optimized JSON in C

    

Suggest a related project