topic Re: mongodb to mysql How to give Structure To My Data in Qlik Replicate

mongodb to mysql How to give Structure To My Data

lakshmanan6991 — Wed, 02 Aug 2023 10:35:07 GMT

how can i give structure to my data coming from mongo_db and write into mysql as a structure data

below is my unstructure data

But I want the data as structured data below I mentioned:-

Any One can Suggest to Achieve this in qlik replicate is possibile or Not

Re: mongodb to mysql How to give Structure To My Data

Shai_E — Wed, 02 Aug 2023 13:15:08 GMT

Hi @lakshmanan6991 ,

I didnt test this out, but from reading the user guide section related to mongodb as a source this seems to be by design:

How target tables are created

For each MongoDB collection, a corresponding target table is created with two columns:

_id - a WSTRING column containing the document's _id field (the primary key).
_doc - an NCLOB column containing the JSON representation of the document.

User guide section:

Overview | Qlik Replicate Help

Re: mongodb to mysql How to give Structure To My Data

john_wang — Wed, 02 Aug 2023 14:40:40 GMT

Hello @lakshmanan6991 ,

I'd like to add additional info besides @Shai_E comments.

I agree with Shai, it's by design. looks to me the best approach is :

1- Replicate the collections to _doc in MySQL

2- Parse and convert the JSON document to 'relational' table within MySQL. It's better if you can define a VIEW to parse the document from table to relational structured row.

More information can be found at MySQL Document Store, & Converting between JSON and non-JSON values.

In MySQL 8.0, there are 2 API(s) to access MySQL tables:

SQL API (Std Protocol)
CRUD and SQL APIs (X Protocol)

It's easy to access JSON and parse the unstructured document as is 'relational' structured rows. for example:

MySQL [localhost+ ssl/nosql] JS> db.demo.find()
[
{
"_id": "0000641484ed0000000000000001",
"id": "22",
"name": "John"
}
]
1 document in set (0.0009 sec)

MySQL [localhost+ ssl/nosql] SQL> show create table demo\G
*************************** 1. row ***************************
Table: demo
Create Table: CREATE TABLE `demo` (
`_doc` json DEFAULT NULL,
`_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`_doc`,_utf8mb4'$._id'))) STORED NOT NULL,
PRIMARY KEY (`_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.0015 sec)

MySQL [localhost+ ssl/nosql] SQL> select _doc->>'$.name' from demo;
+----------------+
| _doc->>'$.name'|
+----------------+
| John |
+----------------+
1 row in set (0.0010 sec)

MySQL [localhost+ ssl/nosql] SQL>

Hope this helps.

Regards,

John.

Re: mongodb to mysql How to give Structure To My Data

Shai_E — Wed, 02 Aug 2023 14:43:51 GMT

Hi @john_wang ,

This is very good stuff John.

Thank You.