t0r0X/jq generate sql.md

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

JQ Manual

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

JQ to filter JSON by value

Syntax: cat <filename> | jq -c '<filter> | select( <subfilter> )'

Examples:

Get JSON record with \_id equal 611:

cat my.json | jq -c '.[] | select( ._id == 611)'

Get JSON record with name containing ck:

cat my.json | jq -c '.[] | select( ._id == 611)'

Get child JSON record with fields having a certain value:

curl the-url | jq '._embedded.valueList[] | select( .officenumber == "99A")'