mongodb to mysql How to give Structure To My Data

[lakshmanan6991]

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

[Shai_E]

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
  
  

[john_wang · Accepted solution]

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.

[Shai_E]

Hi @john_wang ,

This is very good stuff John.

Thank You.