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

  1. JSON string:

    Copy 
    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

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

    Copy 
    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.

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

    Copy 
    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
    }'

  4. Parse out array object into separate lines:

    Copy 
    SOURCE_ENV="Oracle Target"
    lines=`echo ${json} | cut -d "[" -f2 | cut -d "]" -f1 | awk -v RS="},{" -F: '{print $0}'`

    while read -r line
    do
        # 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
        fi
    done <<<< "$(echo -e "$lines")"

    echo "primaryUser reference: ${PRI_USER}"

  5. Output:

    Copy 
    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:

Mac:

 jq 

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

64-bit system:

Older versions:

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

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

Example: Use Python to pretty format the JSON data string

  1. Pretty JSON using Python:

    Copy 
    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
    }'

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

    Copy 
    $ 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:

Example:

Copy 
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.

  1. Pipe JSON string into jq command line parser. The output is a pretty human-readable JSON formatted string.

    Copy 
    ActionScript 

    echo $json | jq '.

  2. Get the first-level status value ( . . . ,"status":"OK", . . . )

    Copy 
    ActionScript

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

  3. Get raw values (not quoted).

    Copy 
    ActionScript

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

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

    Copy 
    ActionScript

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

  5. Get the first result set.

    Copy 
    {
        "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": 2409529344.0,
                    "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": 2410053632.0,
                    "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
    }

  6. Get the first result set name value.

    Copy 
    ActionScript

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

  7. Get first result set reference value.

    Copy 
    ActionScript

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

  8. Get first result set name=value pairs.

    Copy 
    ActionScript

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

  9. Get ALL result sets name values.

    Copy 
    ActionScript

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

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

    Copy 
    ActionScript

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

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

    Copy 
    echo $json | jq --raw-output '.result[] | select(.name=="VBITT") | .container' 
    # Output: ORACLE_DB_CONTAINER-121

    echo $json | jq --raw-output '.result[] | select(.name=="VBITT") | .reference' 
    # Output: ORACLE_VIRTUAL_SOURCE-25

    echo $json | jq --raw-output '.result[] | select(.name=="VBITT") | .container, .reference'
    # Output: ORACLE_DB_CONTAINER-121 ORACLE_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.

    Copy 
    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

  1. 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.

    Copy 
    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)
    }

  2. Use the JSON from the system API Call.

    Copy 
    $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
    }'

  3. Convert the JSON string.

    The job and action are null values.
    Copy 
    PS> $o = ConvertFrom-Json
    PS> $o Key                  Value
    ---                          -----
    type                         OK
    Resultstatus                 OK
    result                       {[type, SystemInfo], [productType, standard], [productNa...jobaction}

  4. Extract the result JSON string array.

    Copy 
    PS> $a = $o.result
    PS> $a Key                        Value
    ---                              -----
    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]...}
    configured                        True
    enabledFeatures                  {XPP, MSSQLHOOKS}
    apiVersion                       {[type, APIVersion], [major, 1], [minor, 8], [micro, 0]}
    bannerlocals                     {enUS}
    currentLocale                    enUS
    hostname                         Delphix5110HWv8
    sshPublicKey                     ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDOsrp7Aj6hFQh9yBq7...
    memorySize                       8581079040
    platform                         VMware with BIOS date 05/20/2014
    uuid                             564d7e1df4cb-f91098fd348d74817683
    processors                       {System.Collections.Generic.Dictionary`2[System.String,S...}
    storageUsed                      2158171648
    storageTotal                     20673724416
    installationTime                 2016-07-27T13:28:46.000Z

  5. Same output as above.

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

  6. Extract the result.buildVersion object.

    Copy 
    PS> $a1 = $o.result.buildVersion
    PS> $a1 Key                       Value
    ---                               -----
    type                              VersionInfo
    major                             5
    minor                             1
    micro                             1
    patch                             0
    PS> $a1.major                     5

  7. Extract the result.processors array collection.

    Copy 
    PS> $b = $o.result.processors
    PS> $b Key                          Value
    ---                                -----
    type                               CPUInfo
    speed                              2500000000
    cores                              1
    PS> $a -is [Array]                False
    PS> $a -is [Object]               True
    PS> $b -is [Array]                True

  8. Convert Array Collection to Object.

    Copy 
    PS> $b1 = $b | Select-Object
    PS> $b1 Key                          Value
    ---                                -----
    type                               CPUInfo
    speed                              2500000000
    cores                              1
    PS> $b1.type                        CPUInfo
    PS> $b1.speed                       2500000000

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.