Victor t0r0X

Processing JSON using jq

jq is useful to slice, filter, map and transform structured json data.

Installing jq

On Mac OS

brew install jq

jqPlay snippets collected using google and github

https://jqplay.org/s/8Zrwy7dDxW

map(select(.|length > 9))

https://jqplay.org/s/Jb_fnBveMQ

with_entries(.value |= .ip)

We investigated the tool jq.

Wes first encountered this as a query tool for JSON. Our probing with the tool lead us to believe that it is much more than this.

This author intends to investigate JSONPath in the future, as it feels targetted specifically at querying. jq is capable of arbitrary JSON transformations. There is also JMESpath which is described as query language for JSON (this author was under the impression JMESpath was also capable of JSON transformations, albeit a more limited set than jq allows for the expression of)

First we needed some JSON. @ryanagar noted the considerable breadth of Yukon GIS data. With the help of a query builder, we were able to pull down a blob, and the task for the group was to investigate it's structure w

jq Quickstart

Usage

echo '{"foo": {"bar": 1}}' | jq .foo.bar

Update software:
- softwareupdate --all --install
- softwareupdate --all --install --force
Install command line tools:
sudo xcode-select --install

Generate SQL statements, with placeholders like ^ for open parenthesis, ~ for closed parenthesis, and ! for ', because jq does not honor all escapes. Therefore requires post-processing with search & replace:

cat  .../data.json | \
  jq -r '.[] | ["insert into person^version, created_timestamp, modified_timestamp, extern, vorname, nachname, kennung, org_unit, telefonnummer, raumnummer, geburtsdatum, anschrift, eintrittsdatum, ausweis_nummer, ausweis_ablaufdatum, anmerkungen, firma_id~ values ^1, systimestamp, systimestamp, !Y!, !", .vorname, "!, !", .nachname, "!, !", .kennung, "!, !", .email, "!, !", .organisation, "!, !", .telefonnummer, "!, !", (.raumnummer|tostring), "!, !", .geburtsdatum, "!, !!, !", .beginntermin, "!, !", .ausweisNummer, "!, !", .ausweisGueltigkeit, "!, !!, ", (1|tostring), "~;"] | add' | \
  tr -d '"' | tr "^~!" "()'" \
  > .../V1.0.0_2__init-testdata.sql

	# Kudos @ Christian S.
	# Changes might need restarting the Windows desktop ("explorer.exe") or logging out and back in, or rebooting the computer.

	# query, might fail if key does not exist
	Get-ItemProperty -Path HKCU:\Software\Microsoft\Windows\CurrentVersion\Explorer\Advanced -Name ShowSecondsInSystemClock

	# enable
	New-ItemProperty -Path HKCU:\Software\Microsoft\Windows\CurrentVersion\Explorer\Advanced -Name ShowSecondsInSystemClock -PropertyType DWord -Value 1

	# disable

	import com.sun.jna.platform.win32.Advapi32Util;
	import com.sun.jna.platform.win32.WinReg;

	/*
	<dependency>
	<groupId>net.java.dev.jna</groupId>
	<artifactId>jna</artifactId>
	<version>4.2.0</version>
	</dependency>
	<dependency>

	# See https://stedolan.github.io/jq/manual/

	echo '{}' \| jq 'if isempty(. \| arrays) then "error" else . \| arrays \| length end'
	# error

	echo '[]' \| jq 'if isempty(. \| arrays) then "error" else . \| arrays \| length end'
	# 0

	echo '{}' \| jq 'if isempty(. \| arrays) then "input not an array"\|halt_error(1) else . \| arrays \| length end'
	# input not an array

	try (tonumber) catch ("Should catch error")
	"a20"
	"Should catch error"

	# Tests are groups of three lines: program, input, expected output
	# Blank lines and lines starting with # are ignored

	#
	# Simple value tests to check parser. Input is irrelevant
	#