JSON parsing

Unix/Linux/Mac shell

Unix/Linux tools come natively with a host of shell utilities that one can use for parsing out the desired name/value pairs. Tools include sed, awk, cut, tr, and grep, to name a few. System administrators use these utilities frequently and may be able to assist with the methods for parsing JSON strings. For more information please refer to Parsing JSON with UNIX tools and Extract a JSON value from a BASH script

Basic awk and sed parsing

CODE


json='{"type":"OKResult","status":"OK","result":{"type":"Job","reference":"JOB-53","namespace":null,"name":null,"actionType":"DB_SYNC","target":"ORACLE_DB_CONTAINER-9","targetObjectType":"OracleDatabaseContainer","jobState":"RUNNING","startTime":"2016-08-12T19:58:59.811Z","updateTime":"2016-08-12T19:58:59.828Z","suspendable":true,"cancelable":true,"queued":false,"user":"USER-2","emailAddresses":null,"title":"Run SnapSync for database \"VDPXDEV1\".","percentComplete":0.0,"targetName":"Oracle_Source/VDPXDEV1","events":[{"type":"JobEvent","timestamp":"2016-08-12T19:58:59.840Z","state":null,"percentComplete":0.0,"messageCode":"event.job.started","messageDetails":"DB_SYNC job started for \"Oracle_Source/VDPXDEV1\".","messageAction":null,"messageCommandOutput":null,"diagnoses":[],"eventType":"INFO"}],"parentActionState":"WAITING","parentAction":"ACTION-238"},"job":null,"action":null}' echo $json | sed -e 's/[{}]/''/g' | awk -v RS=',' -F: '{print $1 $2}'"type""OKResult""status""OK""result""type""reference""JOB-53""namespace"null"name"null"actionType""DB_SYNC""target""ORACLE_DB_CONTAINER-9""targetObjectType""OracleDatabaseContainer""jobState""RUNNING""startTime""2016-08-12T19"updateTime""2016-08-12T19"suspendable"true"cancelable"true"queued"false"user""USER-2""emailAddresses"null"title""Run SnapSync for database \"VDPXDEV1\".""percentComplete"0.0"targetName""Oracle_Source/VDPXDEV1""events"["type""timestamp""2016-08-12T19"state"null"percentComplete"0.0"messageCode""event.job.started""messageDetails""DB_SYNC job started for \"Oracle_Source/VDPXDEV1\".""messageAction"null"messageCommandOutput"null"diagnoses"[]"eventType""INFO"]"parentActionState""WAITING""parentAction""ACTION-238""job"null"action"null

Find jobState. Print the second argument, and remove the double-quotes.

CODE


echo $json | sed -e 's/[{}]/''/g' | sed s/\"//g | awk -v RS=',' -F: '$1=="jobState"{print $2}' RUNNING

The first sed removed the brackets and braces. The second sed removes the double-quotes. The awk command parses the line by comma delimiters and then parses each line by the semi-colon delimiter and if the first variable $1 is equal to the jobState value then print the second $2 variable.

If the results contain an array of values, then you need to loop through each set and parse out the desired value. For example,

CODE


json='{"type":"ListResult","status":"OK","result":[{"type":"WindowsHostEnvironment","reference":"WINDOWS_HOST_ENVIRONMENT-1","namespace":null,"name":"Window Target","description":"","primaryUser":"HOST_USER-1","enabled":false,"host":"WINDOWS_HOST-1","proxy":null},{"type":"UnixHostEnvironment","reference":"UNIX_HOST_ENVIRONMENT-3","namespace":null,"name":"Oracle Target","description":"","primaryUser":"HOST_USER-3","enabled":true,"host":"UNIX_HOST-3","aseHostEnvironmentParameters":null}],"job":null,"action":null,"total":2,"overflow":false}'

Parse out array object into separate lines

CODE


SOURCE_ENV="Oracle Target"lines=`echo ${json} | cut -d "[" -f2 | cut -d "]" -f1 | awk -v RS='},{}' -F: '{print $0}' `while read -r linedo    #echo "Processing $line"    #echo $line | sed -e 's/[{}]/''/g' | sed s/\"//g | awk -v RS=',' -F: '$1=="name"{print $2}'    TMPNAME=`echo $line | sed -e 's/[{}]/''/g' | sed s/\"//g | awk -v RS=',' -F: '$1=="name"{print $2}' `    #echo "Name: |${TMPNAME}| |${SOURCE_ENV}|"    if [[ "${TMPNAME}" == "${SOURCE_ENV}" ]]    then        echo $line | sed -e 's/[{}]/''/g' | sed s/\"//g | awk -v RS=',' -F: '$1=="primaryUser"{print $2}'        PRI_USER=`echo $line | sed -e 's/[{}]/''/g' | sed s/\"//g | awk -v RS=',' -F: '$1=="primaryUser"{print $2}' `        break    fidone <<< "$(echo -e "$lines")" echo "primaryUser reference: ${PRI_USER}"

Output:

CODE


primaryUser reference: HOST_USER-3

The above methods will be used within the sample scripts since they use the native Linux tools. They typically do not require you to load extra packages or libraries onto the system.

There are a number of open-source utilities designed to simplify the parsing of JSON, such as jsawk and jq.

jsawk

Linux:

jswak

Mac:

jswak on MAC

jq

Reference- https://stedolan.github.io/jq/

64-bit system:

wget https://github.com/stedolan/jq/releases/download/jq-1.6/jq-linux64
chmod +x ./jq
sudo cp jq /usr/local/bin

Older versions:

Reference- https://stedolan.github.io/jq/download/

Another method is to use an existing programming language typically available with your native operating systems, such as Perl or Python.

CODE


$ which perl/usr/bin/perl$ which python/usr/bin/python

Example: Use Python to pretty format the JSON data string.

Pretty JSON using Python ...

CODE


json='{"type":"OKResult","status":"OK","result":{"type":"SystemInfo","productType":"standard","productName":"Delphix Engine","buildTitle":"Delphix Engine 5.1.1.0","buildTimestamp":"20160721T07:23:41.000Z","buildVersion":{"type":"VersionInfo","major":5,"minor":1,"micro":1,"patch":0},"configured":true,"enabedFeatures":["XPP","MSSQLHOOKS"],"apiVersion":{"type":"APIVersion","major":1,"minor":8,"micro":0},"banner":null,"locals":["enUS"],"currentLocale":"enUS","hostname":"Delphix5110HWv8","sshPublicKey":"ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDOsrp7Aj6hFQh9yBq7273B+qtPKmCu1B18nPvr08yjt/IZeM4qKk7caxExQS9rpfU8AWoT7e8ESV7NkBmUzOHrHnLsuJtxPqeYoqeMubVxYjJuxlH368sZuYsnB04KM0mi39e15lxVGvxQk9tyMpl7gs7cXRz1k6puncyiczU/axGq7ALHU2uyQoVmlPasuHJbq23d21VAYLuscbtgpZLAFlR8eQH5Xqaa0RT+aQJ6B1ihZ7S0ZN914M2gZHHNYcSGDWZHwUnBGttnxx1ofRcyN4/qwT5iHq5kjApjSaNgSAU0ExqDHiqgTq0wttf5nltCqGMTFR7XY38HiNq++atDroot@Delphix5110HWv8\n","memorySize":8.58107904E9,"platform":"VMware with BIOS date 05/20/2014","uuid":"564d7e1df4cb-f91098fd348d74817683","processors":[{"type":"CPUInfo","speed":2.5E9,"cores":1}],"storageUsed":2.158171648E9,"storageTotal":2.0673724416E10,"installationTime":"2016-07-27T13:28:46.000Z"},"job":null,"action":null}'

Pipe the JSON data to Python programming language to pretty up the format the output for the $json string/data.

CODE


$ echo $json | python -mjson.tool{    "action": null,     "job": null,     "result": {        "apiVersion": {            "major": 1,             "micro": 0,             "minor": 8,             "type": "APIVersion"        },         "banner": null,         "buildTimestamp": "20160721T07:23:41.000Z",         "buildTitle": "Delphix Engine 5.1.1.0",         "buildVersion": {            "major": 5,             "micro": 1,             "minor": 1,             "patch": 0,             "type": "VersionInfo"        },         "configured": true,         "currentLocale": "enUS",         "enabedFeatures": [            "XPP",             "MSSQLHOOKS"    ],     "hostname": "Delphix5110HWv8",     "installationTime": "2016-07-27T13:28:46.000Z",     "locals": [        "enUS"    ],     "memorySize": 8581079040.0,     "platform": "VMware with BIOS date 05/20/2014",     "processors": [        {            "cores": 1,             "speed": 2500000000.0,             "type": "CPUInfo"        }    ],     "productName": "Delphix Engine",     "productType": "standard",     "sshPublicKey": "ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDOsrp7Aj6hFQh9yBq7273B+qtPKmCu1B18nPvr08yjt/IZeM4qKk7caxExQS9rpfU8AWoT7e8ESV7NkBmUzOHrHnLsuJtxPqeYoqeMubVxYjJuxlH368sZuYsnB04KM0mi39e15lxVGvxQk9tyMpl7gs7cXRz1k6puncyiczU/axGq7ALHU2uyQoVmlPasuHJbq23d21VAYLuscbtgpZLAFlR8eQH5Xqaa0RT+aQJ6B1ihZ7S0ZN914M2gZHHNYcSGDWZHwUnBGttnxx1ofRcyN4/qwT5iHq5kjApjSaNgSAU0ExqDHiqgTq0wttf5nltCqGMTFR7XY38HiNq++atDroot@Delphix5110HWv8\n",    "storageTotal": 20673724416.0,     "storageUsed": 2158171648.0,     "type": "SystemInfo",     "uuid": "564d7e1df4cb-f91098fd348d74817683"    },     "status": "OK",     "type": "OKResult"}

jq parser

The "jq" command line parser is available on Unix, Linux, Mac, and Windows platforms. Typically, for Windows, the built-in ConvertFrom/To-Json object parser will be used. "jq" is being included in most native Linux distributions and is easy to install on the Mac OS.

References:

Parsing JSON with jq

Installation FAQ

Mac Installation

Example:

CODE


json='{"type":"ListResult","status":"OK","result":[{"type":"OracleLinkedSource","reference":"ORACLE_LINKED_SOURCE-52","namespace":null,"name":"DPXDEV01","description":null,"virtual":false,"restoration":false,"staging":false,"container":"ORACLE_DB_CONTAINER-120","config":"ORACLE_SINGLE_CONFIG-40","status":"DEFAULT","runtime":{"type":"OracleSourceRuntime","status":"RUNNING","accessible":true,"databaseSize":2.409529344E9,"notAccessibleReason":null,"databaseMode":"READ_WRITE","lastNonLoggedLocation":"0","activeInstances":[{"type":"OracleActiveInstance","instanceNumber":1,"instanceName":"DPXDEV01","hostName":"linuxtarget.delphix.local"}],"databaseStats":null,"bctEnabled":true,"racEnabled":null,"dnfsEnabled":false,"archivelogEnabled":null},"backupLevelEnabled":false,"rmanChannels":2,"filesPerSet":5,"checkLogical":false,"externalFilePath":null,"encryptedLinkingEnabled":false,"compressedLinkingEnabled":true,"bandwidthLimit":0,"numberOfConnections":1,"enabled":true,"preScript":"","postScript":"","role":"PRIMARY"},{"type":"OracleVirtualSource","reference":"ORACLE_VIRTUAL_SOURCE-25","namespace":null,"name":"VBITT","description":null,"virtual":true,"restoration":false,"staging":false,"container":"ORACLE_DB_CONTAINER-121","config":"ORACLE_SINGLE_CONFIG-47","status":"DEFAULT","runtime":{"type":"OracleSourceRuntime","status":"RUNNING","accessible":true,"databaseSize":2.410053632E9,"notAccessibleReason":null,"databaseMode":"READ_WRITE","lastNonLoggedLocation":"0","activeInstances":[{"type":"OracleActiveInstance","instanceNumber":1,"instanceName":"VBITT","hostName":"linuxtarget.delphix.local"}],"databaseStats":[{"type":"OracleDatabaseStatsSection","sectionName":"Open Transactions","columnHeaders":["Transaction Count"],"rowValues":[{"type":"OracleDatabaseStatistic","statisticValues":["0"]}]},{"type":"OracleDatabaseStatsSection","sectionName":"Session Statistics","columnHeaders":["Current Session","Total Session","High Watermark"],"rowValues":[{"type":"OracleDatabaseStatistic","statisticValues":["2","46","5"]}]},{"type":"OracleDatabaseStatsSection","sectionName":"Top Wait Events","columnHeaders":["Event","Wait Count","Total Wait Time (s)"],"rowValues":[{"type":"OracleDatabaseStatistic","statisticValues":["Disk file operations I/O","13","13"]},{"type":"OracleDatabaseStatistic","statisticValues":["log file sequential read","11","12"]},{"type":"OracleDatabaseStatistic","statisticValues":["control file parallel write","8","8"]},{"type":"OracleDatabaseStatistic","statisticValues":["control file sequential read","6","3"]},{"type":"OracleDatabaseStatistic","statisticValues":["ARCH wait for process start 3","2","2"]},{"type":"OracleDatabaseStatistic","statisticValues":["db file sequential read","9","1"]},{"type":"OracleDatabaseStatistic","statisticValues":["rdbms ipc reply","1","1"]},{"type":"OracleDatabaseStatistic","statisticValues":["JS coord start wait","1","1"]},{"type":"OracleDatabaseStatistic","statisticValues":["os thread startup","2","0"]},{"type":"OracleDatabaseStatistic","statisticValues":["Parameter File I/O","1","0"]}]},{"type":"OracleDatabaseStatsSection","sectionName":"Top SQL by CPU","columnHeaders":["Percentage of Load","SQL Statement"],"rowValues":[]}],"bctEnabled":false,"racEnabled":null,"dnfsEnabled":false,"archivelogEnabled":null},"operations":{"type":"VirtualSourceOperations","configureClone":[],"preRefresh":[],"postRefresh":[]},"mountBase":"/mnt/provision","fileMappingRules":null,"manualProvisioning":null,"configParams":{"memory_target":"1191182336","processes":"150","log_archive_dest_1":"location=/mnt/provision/VBITT/archive/ MANDATORY","_omf":"ENABLED","filesystemio_options":"setall","compatible":"11.2.0.4.0","audit_trail":"NONE","remote_login_passwordfile":"EXCLUSIVE","open_cursors":"300","audit_sys_operations":"FALSE"},"configTemplate":null,"nodeListenerList":[],"enabled":true,"role":"PRIMARY"}],"job":null,"action":null,"total":2,"overflow":false}'

We have a very big JSON string above. Let's perform some basic jq parsing.

Pipe JSON string into jq command line parser.
CODE
```
ActionScript 

echo $json | jq '.'
```
The output is a pretty human-readable JSON formatted string.
Get the first-level status value ( . . . ,"status":"OK", . . . )
CODE
```
ActionScript

echo $json | jq '.status'"OK"
```

Get raw values (not quoted).

CODE

ActionScript

echo $json | jq --raw-output '.status'OK

Get a number of rows returned for the type equal to "ListResult" API returned request.
CODE
```
ActionScript

echo $json | jq --raw-output '.total'2
```

Get the first result set.

CODE

ActionScript

echo $json | jq '.result[0] '{    "type": "OracleLinkedSource",    "reference": "ORACLE_LINKED_SOURCE-52",    "namespace": null,    "name": "DPXDEV01",    "description": null,    "virtual": false,    "restoration": false,    "staging": false,    "container": "ORACLE_DB_CONTAINER-120",    "config": "ORACLE_SINGLE_CONFIG-40",    "status": "DEFAULT",    "runtime": {        "type": "OracleSourceRuntime",        "status": "RUNNING",        "accessible": true,        "databaseSize": 2409529344,        "notAccessibleReason": null,        "databaseMode": "READ_WRITE",        "lastNonLoggedLocation": "0",        "activeInstances": [            {                "type": "OracleActiveInstance",                "instanceNumber": 1,                "instanceName": "DPXDEV01",                "hostName": "linuxtarget.delphix.local"            }        ],        "databaseStats": null,        "bctEnabled": true,        "racEnabled": null,        "dnfsEnabled": false,        "archivelogEnabled": null    },    "backupLevelEnabled": false,    "rmanChannels": 2,    "filesPerSet": 5,    "checkLogical": false,    "externalFilePath": null,    "encryptedLinkingEnabled": false,    "compressedLinkingEnabled": true,    "bandwidthLimit": 0,    "numberOfConnections": 1,    "enabled": true,    "preScript": "",    "postScript": "",    "role": "PRIMARY"}

Get the first result set name value.

CODE

ActionScript

echo $json | jq --raw-output '.result[0].name'DPXDEV01

Get first result set reference value.

CODE

ActionScript

echo $json | jq --raw-output '.result[0].reference'

Get first result set name=value pairs.

CODE

ActionScript

echo $json | jq '.result[0]' | jq -r "to_entries|map(\"(.key)=(.value|tostring)\")|.[]" | grep container container=ORACLE_DB_CONTAINER-120

Get ALL result sets name values.

CODE

ActionScript

echo $json | jq '.result[].name'"DPXDEV01""VBITT"

Get ALL result sets "reference" and "container" values.

CODE

ActionScript

echo $json | jq '.result[].reference,.result[].container'"ORACLE_LINKED_SOURCE-52""ORACLE_VIRTUAL_SOURCE-25""ORACLE_DB_CONTAINER-120""ORACLE_DB_CONTAINER-121"

Now, let's scan ALL result sets for a conditional match and return a related value.

CODE


echo $json | jq --raw-output '.result[] | select(.name=="VBITT") | .container' ORACLE_DB_CONTAINER-121echo $json | jq --raw-output '.result[] | select(.name=="VBITT") | .reference' ORACLE_VIRTUAL_SOURCE-25echo $json | jq --raw-output '.result[] | select(.name=="VBITT") | .container, .reference'ORACLE_DB_CONTAINER-121ORACLE_VIRTUAL_SOURCE-25

This is the typical usage for Delphix, where the human-readable name is provided and we need to look up the object reference, container, status, etc. for the respective name. Some object references are based on expressions such as "and" or "or" conditions.

CODE


echo $json | jq --raw-output '.result[] | select(.environment=="UNIX_HOST_ENVIRONMENT-9" and .name=="/u02/ora/app/product/11.2.0/dbhome_1" ) | .reference '

In this case, the jq select command has an "and" condition in order to correctly identify the target result object index. This is important for getting the correct and single return value for | .reference, since there might be more than one instance within the environment.

For a working example of using the jq JSON parser, see the VDB Init using jq command-line JSON Parser use case, Filename: vdb_init.sh. A version of all the Unix/Linux/Mac shell scripts exists within the code provided. It contains the *_jq.sh within the filename.

PowerShell

Starting with Powershell 3.0, there are ConvertFrom-Json and ConvertTo-Json modules/commands to parse the JSON string data to/from objects. If you are stuck with Powershell 2.x., the next section provides similar functions as a method of working with JSON strings.

These 2.x functions are not 100% the same as the Powershell 3.0 ConvertFrom-Json/ConvertTo-Json modules.

PowerShell 2 example

Filename: parse_2.0.ps1

For Powershell 2.0, there are no JSON-provided functions or commands, so the following will serialize the JSON data to a serialized array.

CODE


function ConvertTo-Json20([object] $item){    add-type -assembly system.web.extensions    $ps_js=new-object system.web.script.serialization.javascriptSerializer    return $ps_js.Serialize($item)}function ConvertFrom-Json20([object] $item){     add-type -assembly system.web.extensions    $ps_js=new-object system.web.script.serialization.javascriptSerializer    # The comma operator is the array construction operator in PowerShell    return ,$ps_js.DeserializeObject($item)}

Use the JSON from the system API Call.

CODE


$json='{"type":"OKResult","status":"OK","result":{"type":"SystemInfo","productType":"standard","productName":"Delphix Engine","buildTitle":"Delphix Engine 5.1.1.0","buildTimestamp":"20160721T07:23:41.000Z","buildVersion":{"type":"VersionInfo","major":5,"minor":1,"micro":1,"patch":0},"configured":true,"enabedFeatures":["XPP","MSSQLHOOKS"],"apiVersion":{"type":"APIVersion","major":1,"minor":8,"micro":0},"banner":null,"locals":["enUS"],"currentLocale":"enUS","hostname":"Delphix5110HWv8","sshPublicKey":"ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDOsrp7Aj6hFQh9yBq7273B+qtPKmCu1B18nPvr08yjt/IZeM4qKk7caxExQS9rpfU8AWoT7e8ESV7NkBmUzOHrHnLsuJtxPqeYoqeMubVxYjJuxlH368sZuYsnB04KM0mi39e15lxVGvxQk9tyMpl7gs7cXRz1k6puncyiczU/axGq7ALHU2uyQoVmlPasuHJbq23d21VAYLuscbtgpZLAFlR8eQH5Xqaa0RT+aQJ6B1ihZ7S0ZN914M2gZHHNYcSGDWZHwUnBGttnxx1ofRcyN4/qwT5iHq5kjApjSaNgSAU0ExqDHiqgTq0wttf5nltCqGMTFR7XY38HiNq++atDroot@Delphix5110HWv8\n","memorySize":8.58107904E9,"platform":"VMware with BIOS date 05/20/2014","uuid":"564d7e1df4cb-f91098fd348d74817683","processors":[{"type":"CPUInfo","speed":2.5E9,"cores":1}],"storageUsed":2.158171648E9,"storageTotal":2.0673724416E10,"installationTime":"2016-07-27T13:28:46.000Z"},"job":null,"action":null}'

Convert the JSON string.

The job and action are null values.

CODE


PS> $o = ConvertFrom-Json20 $jsonPS> $o Key                Value---                -----type            OKResultstatus            OKresult            {[type, SystemInfo], [productType, standard], [productNa...jobaction

Extract the result JSON string array.

CODE


PS> $a = $o.resultPS> $a Key                    Value---                    -----type                SystemInfoproductType            standardproductName            Delphix EnginebuildTitle            Delphix Engine 5.1.1.0buildTimestamp        20160721T07:23:41.000ZbuildVersion        {[type, VersionInfo], [major, 5], [minor, 1], [micro, 1]...configured            TrueenabedFeatures        {XPP, MSSQLHOOKS}apiVersion            {[type, APIVersion], [major, 1], [minor, 8], [micro, 0]}bannerlocals                {enUS}currentLocale        enUShostname            Delphix5110HWv8sshPublicKey        ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDOsrp7Aj6hFQh9yBq7...memorySize            8581079040platform            VMware with BIOS date 05/20/2014uuid                564d7e1df4cb-f91098fd348d74817683processors            {System.Collections.Generic.Dictionary`2[System.String,S...storageUsed            2158171648storageTotal        20673724416installationTime    2016-07-27T13:28:46.000Z

Same output as above.

CODE


PS> foreach ($element in $a) {$element}PS> $a.typeSystemInfoPS> $a.buildTitleDelphix Engine 5.1.1.0PS> $a.hostnameDelphix5110HWv8

Extract the result.buildVersion object.

CODE


PS> $a1 = $o.result.buildVersionPS> $a1Key                    Value---                    -----type                VersionInfomajor                5minor                1micro                1patch                0PS> $a1.major 5

Extract the result.processors array collection.

CODE


PS> $b = $o.result.processors PS> $b Key                        Value---                        -----    type                    CPUInfospeed                    2500000000cores                    1PS> $a -is [Array] FalsePS> $a -is [Object] TruePS> $b -is [Array] True

Convert Array Collection to Object.

CODE


PS> $b1 = $b | Select-Object PS> $b1 Key                    Value---                    ------type                CPUInfospeed                2500000000cores                1PS>    $b1.type CPUInfoPS> $b1.speed2500000000

PowerShell 3 or greater example

Starting with Powershell 3.0, there is are ConvertFrom-Json and ConvertTo-Json commands to parse the JSON data to/from objects.

Reference:

$o = $json | ConvertFrom-Json

There are a number of tutorials and functional examples on the web. Below is an excerpt from the Powershell introduction video for Linux / Mac Open Source announcement.

Powershell JSON ConvertTo-Json and Python Example 15:55 through 21:16

The concept is straightforward:

The ConvertFrom-Json JSON string is converted into a Powershell object that you can reference directly.
The ConvertTo-Json takes the JSON object and converts it to a string.

JSON parsing from within programming languages

Most programming languages provide their own libraries, functions, and methods for parsing JSON data strings into objects/hashes/arrays/xml that the native programming language can easily process.