You can translate the question and the replies:

Extract certain data from a text column

There is a text column in denodo but It's JSONB datatype in datasourse. Here is my query: 1. Can JSONB/JSON datatype in denodo even though I am using JDBC connection to Postgres. 2. If the 1st scenario is not possible how can i split the text column in deondo which is in JSON format? Thanks
07-08-2023 01:50:49 -0400

1 Answer

Hi, After doing some tests, I noticed that Denodo reads JSON and JSONB format as text. Denodo provides the function [JSONPATH]( to navigate JSON strings, you can use it to extract the fields you need creating new columns in a Selection View or similar. For instance: ``` select JSONPATH( '{ "field_a": 1, "field_b": 2, "field_c": 3 }', '$.field_a') from dual(); ``` As a second solution, but I think it has much more complexity, you could: * Define a custom type based on your JSON structure. * Create a base view with your custom type. * Define a custom wrapper to be associated with your Base View. * Alter the search method of your Base View in order use the custom wrapper. You can find an example here: [Processing of Compound Types: Example]( Hope this helps
Denodo Team
08-08-2023 05:27:46 -0400
You must sign in to add an answer. If you do not have an account, you can register here