Skip to content

bheni/sql4json

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

56 Commits
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

About

sql4json was originally developed as a cli to help work with json data in a terminal session. Often json data pulled in from curl, cat, or other command line tools isn't formatted, and is difficult to get the data you are interested in. sql4json looks to tackle the problem by allowing you to send data via a pipe, or an input redirect, and get the data you are interested using SQL syntax.

In choosing to make this project public I have refactored it so that the sql4json package can be imported and used as a library to access json, or dictionary data.

Release Notes (Newest Version: 0.3.0 Released 2014.05.20)

Installation

mac prerequisite
sudo easy_install readline
pip install
sudo pip install sql4json

Update

sudo pip install --upgrade sql4json

Using the sql4json package in your code

Querying JSON
from sql4json.sql4json import *

query = Sql4Json(json_str, sql_str)
results_dictionary = query.get_results()
print str(query)
Querying data in a Dictionary
from data_query_engine import DataQueryEngine

query = DataQueryEngine(dictionary_with_data, sql_str)
results_dictionary = query.get_results()

Command Line Usage

sql4json options [SQL*]

* If SQL is not specified on the command line, an interactive session will begin where you will be prompted to provide SQL queries. Multiple queries can be run on the same data set until "quit" or "exit" is entered

options:
--help - Prints help message
--log-mode - Processes each line as a seperate input
--csv - Flattens data and outputs it as a csv
--csv-with-headers - Flattens data and outputs it as a csv with the first row being the column headers

Getting Data to sql4json

JSON From File
cat input_file.json|sql4json "SELECT * FROM some/place WHERE condition==true"

or

sql4json "SELECT * FROM some/place WHERE condition==true LIMIT 100" <input_file.json
JSON From the web
curl http://httpbin.org/get|sql4json "SELECT * FROM headers"
JSON From the command line
echo '{"key":"value", "key2":"value 2"}'|sql4json "SELECT *"

Supported SQL Statements

Currently sql4json supports select, from, and where clauses which are very similar those seen in standard sql. The difference being that these statements need to work on a hierarchical data set. This is supported by using one of the path seperaters '.', '/', or ''.

SELECT Clause

The SELECT clause is a comma seperated list of hierarchical elements which are relative to the path specified in the FROM clause

FROM Clause

The FROM clause specifies the root of the query. If the root is an array the query is run over each item in the array. Only one FROM clause can be specified, and all paths specified in the SELECT or WHERE clauses are evaluated relative to this path

WHERE Clause

The WHERE clause allows you to limit the data in the result sets. Supported operators are "==", "!=", ">", "<", ">=", "<=", "&&", "and", "||", "or", "in", "!" and supports parenthesis for orderring.

LIMIT Clause

Limits the number of results output

About

Access data within json documents using standard SQL syntax

Resources

License

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published