When a tabular field is set to the JSON content type, EasyCatalog automatically converts the JSON field content into a tabular field. Certain assumptions are made based on the structure of the JSON:
- For an array of objects, each object is as a row and the properties are columns.
- For an array of values, each value is a row.
- For an object, each property is a row.
The JSONPath parameter can be used to return data from a specific point in the Hierarchy. If the JSONPath is prefixed with + the JSON is returned in flat structure.
Examples
Here are examples of the different types:
Array of objects:
1 2 3 4 5 | [ {"name": "Alice", "age": 30}, {"name": "Bob", "age": 25}, {"name": "Charlie", "age": 35} ] |
Array of values:
1 | [ "alice", "bob", "charlie" ] |
Object:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | { "firstName": "John", "lastName": "Doe", "age": 30, "email": "john.doe@example.com", "address": { "street": "123 Main Street", "city": "Anytown", "state": "CA", "postalCode": "12345" }, "isMarried": false, "hobbies": ["reading", "gardening", "hiking"] } |
Named Object Property:
1 2 3 4 5 6 7 8 | { "Total Light Output": { "9W": [7.4, 7.3, 7.6], "12W": [12.4, 12.2, 12.9], "18W": [17.3, 16.9, 18.1], "24W": ["ETC."] } } |
$.’Total Light Output’
Which gives the result:
Notice the values in the second row are JSON arrays. To transform these into something more useable, they can be processed by post processing them using the jsonarraytostring command:
1 2 3 4 5 6 7 8 | for i=1, table:colcount() do json = table:cell(2,i):getcontent(); json = jsonarraytostring(json, "", "", ", ") if json ~= nil then table:cell(2,i):setcontent(json) end end table:rotate():present() |
Which gives:
Working with Flattened JSON
If JSONPath starts with +, or just + is entered in the JSONPath fieldย the JSON is flattened. Using the example above, the table now looks like:
Breaking Out Specific Content
JSONPath can be used to extract specific values from the JSON into custom fields. In the above example, ‘address’ contains additional JSON. Creating an Advanced custom field with the command
1 | return processjson('jsonpath', field('json field'), '$.address') |
Will extract the ‘address’ JSON using JSONPath, which then can be set to Content Type JSON.
.