Search in JSON data fields

When you have migrated your historical database to WorkZone Archive, all your historical data are stored in JSON files, which you can query with search expressions in WorkZone Client.

 

Create a search expression with WorkZone QueryBuilder

When you have mapped the JSON paths to OData, you can create a query in WorkZone QueryBuilder so that you can identify the label that you need to create your search expression.

Locate the label name in WorkZone QueryBuilder

  1. Open WorkZone QueryBuilder
  2. In the Entity section, select Archive JSON Paths (ArchiveJsonPaths).
    If you have not run the SQL*Plus code snippet to map the labels, you can still select this view, but it will be empty.
  3. In the Select section, select the data columns that you want to display in the query results. You must include Context Label (ContextLabel) and JSON Data Paths (JsonDataPaths) to query the data that you need to create your search expression.
  4. In the Filter section, set the following filters for these fields
    • Period (Period) equals <The period scheme that you want to search in>.
    • Register name (registerName) equals <The register that you want to search in; can be either file or record>.
    • Culture name (CultureName) equals <The locale that you want to search in; can be either da-DK, en-GB, de-DE, or ja-JP>.
  5. Click Search.

Create your search expression

The Label column in your query result shows all the label names in your mapped archive JSON file. You can now copy the needed label from the column and insert it in your search expression.

The search expression must follow this syntax:

$?(@."file_name"."file_name_date_dict"."$value"<Search_Operator>"Search_value")

Component Description

"file_name"

Points to the registry that want to search in. Can be either 'file', 'record', 'contact', or 'address'.

"file_name_date_dict"

Points to the Label that you insert from your query in WorkZone QueryBuilder.

"$value" <Search_Operator>

Specify the search operator, you wan to use. Currently, the search function supports the following operators: == , != , > , >= , < , <=

"Search_value"

The value that you want to search for.

In this example, the search expression will show all archive cases where the field update_user that contain the user name USER1.

$?(@."file"."update_user"."$value"=="USER1")

Search using JSON data fields

Prerequisite: Before you can use the Freetext field to search in JSON data fields, your WorkZone administrator must first enable the WorkZone Archive feature in WorkZone Configuration Management > Global > Feature settings > Client > Archive, and create and configure the WorkZone Archive database.

In the Freetext search field, you can perform exact searches for data that are stored in the WorkZone Archive JSON files. To do this, you need a search clause where you specify a JSON path and a search string.