JSON Procedures

This page collects some operations you might have to do with JSON files. This is mostly connect to the PMPC campaign where we extensive work with JSON files.

For most operations you need to have jq installed.

Change a user's value

Scenario: You have to unpublicise a signature from the signatures.json file.

You need:

Execute in Bash Shell:

grep -io "" signatures.json            # Check whether there are multiple signatures with the same email.
                                                    # Per user there are 2 entries (one in the mail body, one in include_vars
                                                    # If there are more than 2, make sure to pick the correct one, and copy it
                                                    # If 2, copy the mail address (case-sensitivity!)

cp -p signatures.json signatures_bak_$(date +%s).json  # copy DB to a safe place, preserving permissions

# REPLACE in user's mail (, desired JSON key (.include_vars.permissionPub), and desired new value (no)
jq -c 'map((select(.include_vars.confirm == "") | .include_vars.permissionPub) |= "no")' signatures.json > signatures_edit.json

# Replace signatures.json if the amount of signatures matches the edited file
if [ $(grep -o "timestamp" signatures_edit.json | wc -l) == $(grep -o "timestamp" signatures.json | wc -l) ]; then 
  echo "Amount of JSON entries matches. signatures.json replaced with edited file."
  mv signatures_edit.json signatures.json

# Fix permissions to be writable for forms
chmod 666 signatures.json

Extract all values of a specific user

Scenario: You want to see a full record of a single user (identified by email address).

jq 'map(select(.include_vars.confirm == "") | . )' signatures.json

To limit this to the data the user put in the form fields, run:

jq 'map(select(.include_vars.confirm == "") | .include_vars )' signatures.json

Extract a specific value of selected users

Scenario: You want to extract all comments of signatories in Germany.

jq 'map(select( == "Germany") | .include_vars.comment | select(length > 0))' signatures.json

| select(length > 0) ignores empty comments

Find a user with limited information

Scenario: You only have a name of a signatory (example: "Kirschner") and need to find the email or other attributes she subscribed with. The search is case-insensitive.

jq 'map(select( | match("Kirschner";"i")) | . )' signatures.json

This may show multiple entries. Try to change the key or search query accordingly.

Scenario: You want to print all users who agreed to receive news in a CSV format.

jq -r 'map(select(.include_vars.permissionNews == "yes") | .include_vars | [.name, .confirm]) | .[] | @csv' signatures.json

If you want to use multiple attributes, e.g. select those who want to receive news and are from Austria:

jq -r 'map(select(( == "Austria") and (.include_vars.permissionNews == "yes")) | .include_vars | [.name, .confirm, .permissionNews]) | .[] | @csv' signatures.json

Delete a user

jq -c '. |= map(select(.include_vars.confirm != ""))' signatures.json > signatures_edit.json
mv signatures_edit.json signatures.json
chmod 666 signatures.json


Note: match() requires jq >= 1.5. Use it on another computer of your jq is too old.

TechDocs/JSON (last edited 2020-06-23 09:10:01 by max.mehl)