Skip to main content

Hi!

I want to extract json schema from a json blob that is stored in a binari datatype field in sql server database. A lot of the fields in my schema are optional, though I want a total overview of all available fields and the entity they belong to. All is now stored in one field, but I want to use the schema to create a data source structure through which I can navigate and ideally also get profile & dq insights from. 

How can I achieve this? Is there something out of the box available for this?

Kind regards, Jur Dördregter

Hello ​@jdordregter ,

semi-structured data are not yet supported out of the box by the web application, however you can try to use Virtual Catalog Item and JSON Parser/JSON Reader step to parse the JSON.

You say it is stored in SQL Server so I would try one of those options:

  • connect the MSSQL to the web application and import the table with this blob. Then create a Virtual Catalog Item (VCI) where you would read this table with this blob using Catalog Item Reader, and then parse the JSON field  using the JSON parser.
  • connect to MSSQL inside the Virtual Catalog Item directly to get just the field you need, and again use JSON parser to parse the field. In this case you need to define the connection to the MSSQL in the Global runtime configuration: https://docs.ataccama.com/one/latest/dpm-admin-console/dpm-admin-console.html#configuration

Link to the documentation for the VCI: https://docs.ataccama.com/one-desktop/latest/work-with-ataccama-one/virtual-catalog-items.html

This approach isn’t ideal as it is very sensitive to any changes inside the JSON schema, however there is no other way at the moment. Semi-structured formats are on our roadmap, though, so we should have better mechanisms in the future.

Please let me know if this is something that could help you.

Kind regards,

Anna

Reply


Community Terms of Use & ConditionsCookie settings