You can translate the question and the replies:

Compound types denodo - Mongodb

Hi, Context : -I have different documents within a collection in mongodb : - I have the collection with document A and document B - In the document A , I have an element "F1" of type "Array of objects" with sub-fileds "sub-Fi" - In document B, I have the same element "F1" but of type "object" because it only contain one element. When importing those documents in denodo, i tried to specify the type in the box "field" as "Array". I tried two different syntax, both returned "ERROR OF TYPE 2002" when quering the views. - F1 : array(Varchar) - F1 : array( \{ 0 : \{SUB F1 : VARCHAR, SUB F2: VARCHAR\}, 1 : \{SUB F1: VARCHAR, SUB F2: VARCHAR, SUB F3: VARCHAR\} \}) Is there a way to import those document in the same type with a right syntax and the right format? Thank you,
Register mongodb Compound Types ARRAY Type Conversion Error
user
16-02-2023 06:38:27 -0500
code

3 Answers

Hi, When we work with MongoDB in Denodo, different types can be used in the same element of a document. In this case, the documentation says that we should set those elements with the type VARCHAR (text in VDP), as you can see in this documentation, in the "Schemas" -> "Specifying Fields" section: https://community.denodo.com/docs/html/document/denodoconnects/8.0/en/Denodo%20MongoDB%20Custom%20Wrapper%20-%20User%20Manual "If documents in the same collection specify different types for fields with the same name, using VARCHAR (text in VDP) for that column will automatically perform the required conversions to show data from those documents." Also you can edit the data in document B to create an array of one object, so you can import everything as arrays of objects. Hope this helps!
Denodo Team
17-02-2023 04:18:32 -0500
code
Hi, Thank you for the answer, I already saw the documentation. When I get the element as Text, I have a weird format such as : "Document{{sub-elem1 , sub-elem2..}}.Document{{sub-elem1 , sub-elem2..}}" ==> so how can I use this format to extract sub-elem1, sub-elem2?? I tried using function like split but it's realy hard to access the element. If I want to edit the data in document B to create an array of one object, at what level I can do that in denodo? When specifing the type as "Array" in the "Field" Section, I get an error when displaying the view? Also, in the documentation, they are saying we can edit a field as "JSON" type in the section "Fields" but Denodo throws an error whenever I try that? Thank you,
user
17-02-2023 04:57:21 -0500
Hi, I realise that having the array data as text makes it hard to transform, but when you have different data types on the same field there is no other easier approach to do this. Edit document B to create an array of one object must be done on the data origin, the mongodb database, so then Denodo can import it as an array. About the JSON type, i made a test with a field that have a string "Test" in document A and an array on the rest of documents; i applied this schema when creating the base view (_id:VARCHAR,transactions:JSON), and that worked ok for me. Hope this helps!
Denodo Team
20-02-2023 03:27:59 -0500
code
You must sign in to add an answer. If you do not have an account, you can register here