How to flatten the JSON Data in Bold BI?
In Bold BI, you can flatten JSON data using expressions. This process is particularly useful when you want to extract specific values from a JSON object or array. The following guide provides examples of how to flatten JSON data when your IMDB is MySQL.
The queries are explained using the below sample JSON data,
[{
"test_name": "IELTS1",
"test_id": "T1",
"scores": [7.5, 8.0, 8.0, 9.0],
"user": {
"name": "user1",
"age": 30
},
"arr": [{
"val": "ar1"
},
{
"val": "ar2"
}
]
},{
"test_name": "IELTS2",
"test_id": "T2",
"scores": [7.5, 8.0, 8.0, 9.0],
"user": {
"name": "user2",
"age": 30
},
"arr": [{
"val": "ar12"
},
{
"val": "ar22"
}
]
},{
"test_name": "IELTS3",
"test_id": "T3",
"scores": [7.5, 8.0, 8.0, 9.0],
"user": {
"name": "user3",
"age": 30
},
"arr": [{
"val": "ar31"
},
{
"val": "ar32"
}
]
}]
Flattening JSON Data in Bold BI with MySQL
Extracting First Level Object
To extract the first level object from your JSON data, use the following expression:
JSON_UNQUOTE(JSON_EXTRACT([log], ‘$.test_id’))
Extracting First Level Array
To extract the first level array from your JSON data, use the following expression:
JSON_EXTRACT(log, ‘$.scores’)
Extracting Selected Array Values
To extract selected values from an array in your JSON data, use the following expression:
JSON_EXTRACT([log] -> ‘$.scores’, ‘$[0]’)
Extracting Values from Nested Object
To extract values from a nested object in your JSON data, use the following expression:
JSON_EXTRACT([log], ‘$.user’)
To extract specified values from a nested object in your JSON data, use the following expression:
JSON_EXTRACT([log], ‘$.user.age’)
Extracting Selected Array Values from Nested Array
To extract selected values from a nested array in your JSON data, use the following expression:
JSON_EXTRACT([log], ‘$.arr[0].val’)
Flattening JSON Data in Bold BI with PostgreSQL
In PostgreSQL it has jsonb data type, So we have default functions to flatten the JSON value. Use the below query in the expression field.
Extracting First Level Object
To extract the first level object from your JSON data, use the following expression:
jsonb_array_elements([log]::jsonb)->>'test_id'
Extracting Values from Nested Object
To extract values from a nested object in your JSON data, use the following expression:
jsonb_array_elements([log]::jsonb)->'user'
To extract specified values from a nested object in your JSON data, use the following expression:
jsonb_array_elements([log]::jsonb)->'user'->>'name'
Extracting Selected Array Values from Nested Array
To extract selected values from a nested array in your JSON data, use the following expression:
jsonb_array_elements(jsonb_array_elements([log]::jsonb)->'arr')->>'val'
Flattening JSON Data in Bold BI with MS SQL
For MS SQL Server, you can flatten JSON data using code view mode
This is the sample code snippet to flatten the JSON data.
SELECT
<column_names..>,
[JSON_column]
FROM <table_name> AS <alias name>
CROSS APPLY OPENJSON([<JSON_column>]) WITH ([<JSON_column>] <data_type>) '$.[JSON_Key]')
By adding the JSON_Key we get the multi-level JSON values: $.[JSON_Key1].[JSON_Key2].[JSON_Key2]
By using these expressions/code view mode, you can effectively flatten your JSON data in Bold BI.
Additional References
