Importing JSON string from SQL Server datatabase
We have data fields in our SQL Server database that are in JSON format that we would like to bring into Sisense and report on some of the fields. Is Sisense able to bring in the JSON formatted data and parse it into a table and fields? If so, how can this be done? We do have the JDBC Connector drivers installed. Not sure if we can use this some how and create a customer query using SQL.
Hi,
The easiest way to parse the JSON data stored within a column is to parse it on the SQL database side. There are native SQL functions that are capable of parsing json objects and flattening them into individual fields. Alternatively, you can try the import sql query editor feature in Sisense but it might impact the ElastiCube build time. Both approaches are described in the community article "Flattening JSON Objects in SQL Server".
If you like to connect to JSON files as a datasource, it also might be possible to achieve that with the help of JDBC driver. Please reach out to our Support Team to get more details on this option.
Hope it helps!
Regards, Lily
