21.8. Transforming JSON Data (json_transform, json_transform_strict) #
The json_transform and json_transform_strict stored procedures transform JSON data into the specified structure that defines target data types and nesting. The json_transform_strict procedure returns an error when data type casting fails, while the json_transform procedure returns NULL for values that cannot be cast.
Required privileges: Postgres Pro AXE administrator only. For a full list of stored procedures and privileges, refer to Section 12.1.
Execute one of the following commands on the Postgres Pro AXE server:
SELECT json_transform(JSON,structure) ON CONFLICT DO NOTHING;
SELECT json_transform_strict(JSON,structure) ON CONFLICT DO NOTHING;
Where:
JSON: The JSON data that must be transformed.You must specify a JSON string literal or a column with JSON data.
structure: The structure to which the JSON data must be transformed, specified as aJSONvalue. The structure must use the same format as the output of thejson_structurestored procedure.
Postgres Pro AXE returns the JSON data transformed into the specified structure. If the structure contains keys that are missing from the JSON data, they are returned as NULL.
Example 21.8. Executing the json_transform and json_transform_strict Stored Procedures
JSON data in the j column of the example table:
{"family": "anatidae", "species": ["duck", "goose"], "coolness": 42.42}
{"family": "canidae", "species": ["labrador", "bulldog"], "hair": true}
Transforming JSON data:
SELECT json_transform(j::json, '{"family": "VARCHAR", "coolness": "DOUBLE"}') FROM example;
{'family': anatidae, 'coolness': 42.420000}
{'family': canidae, 'coolness': NULL}