avz / Jl Sql
Licence: mit
SQL for JSON and CSV streams
Stars: ✭ 44
Programming Languages
javascript
184084 projects - #8 most used programming language
shell
77523 projects
Projects that are alternatives of or similar to Jl Sql
Nano Sql
Universal database layer for the client, server & mobile devices. It's like Lego for databases.
Stars: ✭ 717 (+1529.55%)
Mutual labels: json, csv, sql
Bancosbrasileiros
Lista de bancos brasileiros | Brazilian banks list
Stars: ✭ 178 (+304.55%)
Mutual labels: json, csv, sql
Countries
Countries, Languages & Continents data (capital and currency, native name, calling codes).
Stars: ✭ 656 (+1390.91%)
Mutual labels: json, csv, sql
Octosql
OctoSQL is a query tool that allows you to join, analyse and transform data from multiple databases and file formats using SQL.
Stars: ✭ 2,579 (+5761.36%)
Mutual labels: json, csv, sql
Trdsql
CLI tool that can execute SQL queries on CSV, LTSV, JSON and TBLN. Can output to various formats.
Stars: ✭ 593 (+1247.73%)
Mutual labels: json, csv, sql
Countries States Cities Database
🌍 World countries, states, regions, provinces, cities, towns in JSON, SQL, XML, PLIST, YAML, and CSV. All Countries, States, Cities with ISO2, ISO3, Country Code, Phone Code, Capital, Native Language, Timezones, Latitude, Longitude, Region, Subregion, Flag Emoji, and Currency. #countries #states #cities
Stars: ✭ 1,130 (+2468.18%)
Mutual labels: json, csv, sql
Algeria Cities
The list of all Algerian provinces and cities according to the official division in different formats: csv, xlsx, php, json, etc.
Stars: ✭ 232 (+427.27%)
Mutual labels: json, csv, sql
Datasette
An open source multi-tool for exploring and publishing data
Stars: ✭ 5,640 (+12718.18%)
Mutual labels: json, csv, sql
World countries
Constantly updated lists of world countries and their associated alpha-2, alpha-3 and numeric country codes as defined by the ISO 3166 standard, available in CSV, JSON , PHP and SQL formats, in multiple languages and with national flags included
Stars: ✭ 598 (+1259.09%)
Mutual labels: json, csv, sql
Structured Text Tools
A list of command line tools for manipulating structured text data
Stars: ✭ 6,180 (+13945.45%)
Mutual labels: json, csv
Goloc
A flexible tool for application localization using Google Sheets.
Stars: ✭ 42 (-4.55%)
Mutual labels: json, csv
Pmacct
pmacct is a small set of multi-purpose passive network monitoring tools [NetFlow IPFIX sFlow libpcap BGP BMP RPKI IGP Streaming Telemetry].
Stars: ✭ 677 (+1438.64%)
Mutual labels: json, sql
Sheetjs
📗 SheetJS Community Edition -- Spreadsheet Data Toolkit
Stars: ✭ 28,479 (+64625%)
Mutual labels: json, csv
Ps Webapi
(Migrated from CodePlex) Let PowerShell Script serve or command-line process as WebAPI. PSWebApi is a simple library for building ASP.NET Web APIs (RESTful Services) by PowerShell Scripts or batch/executable files out of the box.
Stars: ✭ 24 (-45.45%)
Mutual labels: json, csv
Municipios Brasileiros
🏡 Código IBGE, nome do município, capital, código UF, UF, estado, latitude e longitude das cidades brasileiras
Stars: ✭ 638 (+1350%)
Mutual labels: csv, sql
jl-sql
- SQL for JSON and CSV files
CLI frontend for https://github.com/avz/node-jl-sql-api
Installation
Warning: Node.js v6+ required
% sudo npm install -g jl-sql
Examples
Test dataset
% cat data.json
{"key": 2, "value": 2}
{"key": 1, "value": 3}
{"key": 3, "value": 6}
{"key": 3, "value": 4}
{"key": 1, "value": 5}
{"value": 7}
{"key": null, "value": 8}
GROUP BY
% cat data.json | jl-sql 'SELECT key, SUM(value) AS sum, COUNT(*) AS count GROUP BY key'
{"sum":7,"count":1}
{"key":1,"sum":8,"count":2}
{"key":2,"sum":2,"count":1}
{"key":3,"sum":10,"count":2}
{"key":null,"sum":8,"count":1}
ORDER BY
% cat data.json | jl-sql 'SELECT * ORDER BY key'
{"value":7}
{"key":1,"value":3}
{"key":1,"value":5}
{"key":2,"value":2}
{"key":3,"value":6}
{"key":3,"value":4}
{"key":null,"value":8}
WHERE
Use binding
% cat data.json | jl-sql --bind :key=1 'SELECT * WHERE key = :key'
or same thing inline
% cat data.json | jl-sql 'SELECT * WHERE key = 1'
will output
{"key":1,"value":3}
{"key":1,"value":5}
JOIN
We will need 2 files
% cat data.json
{"key": 2, "value": 2}
{"key": 1, "value": 3}
{"key": 3, "value": 6}
{"key": 3, "value": 4}
{"key": 1, "value": 5}
{"value": 7}
{"key": null, "value": 8}
% cat keyInfo.json
{"key": 1, "title": "first"}
{"key": 2, "title": "second"}
{"key": 3, "title": "third"}
% cat data.json | jl-sql --bind :info=keyInfo.json 'SELECT key, @info.title LEFT JOIN {:info} ON @info.key = key'
{}
{"key":1,"title":"first"}
{"key":1,"title":"first"}
{"key":2,"title":"second"}
{"key":3,"title":"third"}
{"key":3,"title":"third"}
{"key":null}
CSV support
% cat data.csv
key,value
2,2
1,3
3,6
3,4
1,5
,7
From stdin:
cat data.csv | jl-sql 'SELECT * FROM CSV(STDIN())'
From file:
jl-sql -b :data=data.csv 'SELECT * FROM CSV({:data})'
Output:
{"key":"2","value":"2"}
{"key":"1","value":"3"}
{"key":"3","value":"6"}
{"key":"3","value":"4"}
{"key":"1","value":"5"}
{"key":"","value":"7"}
Options
You can customize parsing by options:
cat data.csv | jl-sql 'SELECT * FROM CSV(STDIN(), {columns: ["a", "b"], encoding: "cp1251"})'
{"a":"key","b":"value"}
{"a":"2","b":"2"}
{"a":"1","b":"3"}
{"a":"3","b":"6"}
{"a":"3","b":"4"}
{"a":"1","b":"5"}
{"a":"","b":"7"}
List of available options:
-
detectNumbers
- iftrue
, the parser will attempt to convert input string to native types -
detectDates
- iftrue
, the parser will attempt to convert input string to dates. It requires the "auto_parse" option -
columns
- list of fields as an array. By default autodiscovered in the first CSV line -
delimiter
- field delimiter, one character only. Defaults to,
(comma) -
ltrim
- iftrue
, ignore whitespace immediately following the delimiter. Does not remove whitespace in a quoted field. -
rtrim
- iftrue
, ignore whitespace immediately preceding the delimiter. Does not remove whitespace in a quoted field. -
skipEmptyLines
- Don't generate records for lines containing empty column values (column matching/\s*/
). Disable by default -
encoding
- input encoding
Usage
% jl-sql --help
Usage: jl-sql [OPTIONS] SQL
OPTIONS:
-h, --help show this help
-I, --ignore-json-error ignore broken JSON
-v, --verbose display additional information
-S, --sort-external-buffer-size=SIZE use SIZE bytes for `sort` memory buffer
-B, --sort-in-memory-buffer-length=ROWS save up to ROWS rows for in-memory sort
-T, --temporary-directory=DIR use DIR for temporaries, not $TMPDIR or /tmp
-b, --bind=BIND=VALUE+ bind valiable
See full documentation at https://github.com/avz/jl-sql
Note that the project description data, including the texts, logos, images, and/or trademarks,
for each open source project belongs to its rightful owner.
If you wish to add or remove any projects, please contact us at [email protected].