Selectively return JSON properties

Selectively return JSON properties in the JSON DB

Abstract

selectively return JSON properties in the JSON DB

Most query actions allow you to specify which JSON properties to include or exclude from each record that is returned in the results. To do this, you use either the "includePaths" or the "excludePaths" properties. These two properties are mutually exclusive.

Three possible options:

Omit "includePaths" and "excludePaths" to return all JSON fields as is. This is the default.
Use only "includePaths" to specify each JSON path you want returned. No properties outside those paths will be returned.
Use only "excludePaths" to specify each JSON path you want to be omitted. All properties outside those paths will be returned.

Use "includeFields" and "excludeFields" to include or exclude an entire JSON field from a result:

Use "includePaths" when one or more fields in the table contain JSON and you want to include only specific JSON properties. When a JSON property does not match any JSON path in the array, it is omitted from the results.
Use "excludePaths" when one or more fields in the table contain JSON and you want to exclude one or more of their JSON properties. When a JSON property does not match any JSON path in the array, it is included in the results.

Example 1. Company table

A JSON path starts with a field name in a table followed by a JSON path to the desired JSON property.

The table contains two fields: "company_name" and "email". The type of the "email" field is JSON. The JSON path, "email.domain", matches the "email" field and inside the "email" field it matches the JSON property named "domain". Thus, including "email.domain" in "excludePaths" causes the server to omit "domain": "faircom.com" from the results.

Table 1. Company table

company_table	email
"FairCom"	`{ "name": "support", "domain": "faircom.com" }`

JSON paths can reference properties deeply nested inside JSON. A period separates each level in the hierarchy. In addition, a specific item in an JSON array can be returned using brackets containing the zero-based item number.

For example, the JSON path "company_info.support.personNames[3]" selects the fourth item in the "personNames" array nested inside the support property inside the "support" property inside the company_info field in the company table.

Example 2. A request message that excludes the domain property from the email field

Note

The email field contains JSON, and the exclude path removes the "domain" property and returns only the "name" property.

{
  "requestId": "1",
  "action": "getRecordsByPartialKeyRange",
  "params": 
  {
    "databaseName": "myDatabase",
    "tableName": "persons",
    "indexFilter": 
    {
      "indexName": "NameIndex",
      "partialKey": "Mi" 
    },

     "responseOptions": 
     { 
       "omit": ["error", "fieldDefs" ]
       "dataFormat": "arrays", 

       "includePaths": [],
       "excludePaths": [ "email.domain" ],
     }
    "apiVersion": "1.0",
    "authToken": "anAuthorizationTokenFromTheServer"
  }
}