gb_sql: JSON and SQL tools

This is library of bash scripts to manipulate and copy data between JSON files, bash variables, the Postgresql database, and Scilab scripts.

Installation

Get the git repository. Assuming you keep your git repos in ~/git then:

cd ~/git
git clone lilo.science.ru.nl:/vol/thchem/git/gb_sql
# If you already have it, you may want to update it:
git pull

Next, make sure this directory is in your GB_PATH. This can be done with commands from the gb_bash library (described in gb_bash: managing bash functions ) to achieve this:

gb_addpath -n GB_PATH "$HOME"/git/gb_sql

Jq command

The Jq bash function extends the features of the linux jq (jqlang.github.io) command for processing JSON datastructures. It can input JSON data from its argument, from file, and from bash environment variables. The data can be written to standard output, to file, or to bash environment variables.

First load the Jq bash function with:

gb_load gb_jq.sh
# Or, inside a bash script:
gb_load gb_jq.sh || { echo "$0 ERROR: failed to load gb_jq.sh, exit = $?"; exit 1;}

Jq, input JSON

The Jq bash function can take JSON input as arguments:

Jq city:Nijmegen number:42 ok:true vector:[1,2,3] par:{university:Radboud,number:134}

This returns a JSON data structure to stdout:

{"city":"Nijmegen","number":42,"ok":true,"vector":[1,2,3],"name":{"university":"Radboud"}}

The following data types are detected:

  • numbers: non-negative integers and reals, e.g. 0, 1, 9, 1.2e-5

  • logicals true and false

  • arrays, start with [, e.g, [a,b,c]

  • JSON-objects, start with {

  • strings, otherwise. May alos be forced with double quotes, key:\"7a\"

In other cases, an explicit JSON string can be used with the -s flag:

Jq -s {txt:\"7.3a\",b:-42}

Gives:

{"txt":"7.3a","b":-42}

It is also possible to read from files:

Jq par1.json ../par2.json

Jq, process JSON with jq filter

By default, arguments will overwrite values set before it:

Jq par:{T:273,unit:K} par:{T:291}

gives:

{"par:":{"T":273,"unit":"K"},"par":{"T":291}}

An argument starting with a dot . is used as a filter of the jq command (see https://jqlang.github.io/jq)

Jq a:3 b:7  .c=.a*.b+1

gives:

{"a":3,"b":7,"c":22}

Jq, output to file

Output may be “pretty printed” with the -pp flag:

Jq a:40 par:{fac:0.2} .c=.a*.par.fac -pp

{
  "a": 40,
  "par": {
    "fac": 0.2
  },
  "c": 8
}

Output is written to a file with with -w <filename> option:

Jq a:41 -w par.json
# To also write to stdout use -p or -pp
Jq a:41 -w par.json -p

Jq, i/o with environment variables

To export the JSON data as environment variable DB_CURRENT:

Jq DB_CURRENT a:42
echo $DB_CURRENT
# {"a":42}
# The same can be done with the -e (--export) flag
Jq -e DB_CURRENT a:42
# The -S flag is equivalent to ``-e DB_CURRENT``
Jq -S a:42

The DB_CURRENT environment variable is used by the db_get function in the gsci_lib6/lib_api Scilab library to input data.

To assign a specific element of the JSON data to a bash environment variable:

Jq par:{Etot:42,unit:eV}  E=.par.Etot
echo $E
# 42

To import JSON from a bash variable, simply expand it as string:

export par={a:42}
Jq  "$par" -pp

To import data from the DB_CURRENT bash environment variable use the -G (--get) flag:

Jq -S a:42
Jq -G -p
# {"a":42}

Pg command: PostgreSQL

First, setup database access using Db (see gb_sql.sh).

To create a table:

Pg -t mytable init host varchar UNIQUE, load numeric

Here mytable is the name of the PostgreSQL table. The Pg command will export the table name as $PG_TABLE, so subsequent Pg commands given in the same terminal for the same table do not require the -t table_name argument.

To list the table use:

Pg ls

The above example gives:

 id | host | load | created_at | updated_at
----+------+------+------------+-----------
(0 rows)

The column id is automatically included, it gives each record a unique number (1,2,3,…). The columns created_at and updated_at are also always included.

gb_sc

New project:

Sc init HeCO2fm_v24

After run copy results for plotting on PC or laptop:

Sc get HeCO2fm_v24
cd HeCO2fm_v24/run1
Sc cp