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:

The key for the signature publication, which is .included_vars.permissionPub in this example.
The user's email address she used for signing. In this example, it's user@fsfe.org. Also see the matching/finding procedure which makes it very easy
The desired value for the JSON key, in this example no

Execute in Bash Shell:

grep -io "user@fsfe.org" 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 (user@fsfe.org), desired JSON key (.include_vars.permissionPub), and desired new value (no)
jq -c 'map((select(.include_vars.confirm == "user@fsfe.org") | .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
fi

# 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 == "user@fsfe.org") | . )' signatures.json

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

jq 'map(select(.include_vars.confirm == "user@fsfe.org") | .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(.include_vars.country == "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(.include_vars.name | match("Kirschner";"i")) | . )' signatures.json

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

Print all users with a certain attribute

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((.include_vars.country == "Austria") and (.include_vars.permissionNews == "yes")) | .include_vars | [.name, .confirm, .permissionNews]) | .[] | @csv' signatures.json

Delete a user

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

Troubleshooting

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

TechDocs/JSON