You can translate the question and the replies:

JSON Data Source Array Issue

Hi, I am trying to create a Base View for a JSON based Data Source : [{"a":1, "b":2, "c":3}, {"a":11, "b":22, "c":33}, {"a":111, "b":222, "c":333}, {"a":1111, "b":2222, "c":3333}, {"a":11111, "b":22222, "c":33333}] If I create the view using the default ‘JSON root’, the view returns an array as the first element, rather than the actual array elements that I require (but the data IS visible) : ``` DROP DATASOURCE JSON IF EXISTS abc CASCADE; CREATE DATASOURCE JSON abc ROUTE FTP 'ftp.FtpClientConnectionAdapter' 'ftp://abc' 'anonymous' '' ENCRYPTED; DROP WRAPPER JSON IF EXISTS abc CASCADE; CREATE WRAPPER JSON abc DATASOURCENAME=abc TUPLEROOT '/JSONFile' OUTPUTSCHEMA (jsonfile = 'JSONFile' : REGISTER OF ( jsonarray = 'JSONArray' : ARRAY OF ( jsonarray = 'JSONArray' : REGISTER OF ( a = 'a' : 'java.lang.Integer', b = 'b' : 'java.lang.Integer', c = 'c' : 'java.lang.Integer' ) ) ) ); DROP TYPE IF EXISTS abc_jsonarray_jsonarray CASCADE; CREATE TYPE abc_jsonarray_jsonarray AS REGISTER OF (a:int, b:int, c:int); DROP TYPE IF EXISTS abc_jsonarray CASCADE; CREATE TYPE abc_jsonarray AS ARRAY OF abc_jsonarray_jsonarray; DROP VIEW IF EXISTS abc CASCADE; CREATE TABLE abc I18N us_est ( jsonarray:abc_jsonarray ) CACHE OFF TIMETOLIVEINCACHE DEFAULT ADD SEARCHMETHOD abc( I18N us_est CONSTRAINTS ( ADD jsonarray NOS ZERO () ADD jsonarray NOS ZERO () ADD jsonarray.a NOS ZERO () ADD jsonarray.b NOS ZERO () ADD jsonarray.c NOS ZERO () ) OUTPUTLIST (jsonarray ) WRAPPER (json abc) ); ``` https://ibb.co/BgwHs68 If I then specify ‘JSON root/jsonarray’, the schema view looks correct But no data is returned : ``` DROP DATASOURCE JSON IF EXISTS abc CASCADE; CREATE DATASOURCE JSON abc ROUTE FTP 'ftp.FtpClientConnectionAdapter' 'ftp://abc/' 'anonymous' '' ENCRYPTED; DROP WRAPPER JSON IF EXISTS abc_3208 CASCADE; CREATE WRAPPER JSON abc_3208 DATASOURCENAME=abc TUPLEROOT '/JSONFile/jsonarray' OUTPUTSCHEMA (jsonfile = 'JSONFile' : REGISTER OF ( a = 'JSONFile.jsonarray.a' : 'java.lang.Integer', b = 'JSONFile.jsonarray.b' : 'java.lang.Integer', c = 'JSONFile.jsonarray.c' : 'java.lang.Integer' ) ); DROP VIEW IF EXISTS abc CASCADE; CREATE TABLE abc I18N us_est ( a:int, b:int, c:int ) CACHE OFF TIMETOLIVEINCACHE DEFAULT ADD SEARCHMETHOD abc( I18N us_est CONSTRAINTS ( ADD a NOS ZERO () ADD b NOS ZERO () ADD c NOS ZERO () ) OUTPUTLIST (a, b, c ) WRAPPER (json abc_3208) ); ``` https://ibb.co/bXgGm8F Any advice on how to get this working much appreciated.
user
17-07-2020 05:57:17 -0400

2 Answers

Hi! 1. You can create a base view leaving Tuple root as '/JSONFile'. 1. Then right click the base view in the tree and select New > Flatten 1. Right click jsonarray > Flatten array 'jsonarray'. 1. Save and execute and hopefully you will get the result you want. Have a great weekend!
Denodo Team
 Edited on: 21-07-2020 04:58:33 -0400
Hi Jim - awesome, easy when you know how! That works perfectly, many thanks - have a great weekend and look forward to chatting on Monday. Cheers, Alex.
user
17-07-2020 11:25:32 -0400
You must sign in to add an answer. If you do not have an account, you can register here