How to Parse a strange json output and insert into a db

[Anonymous]

Hi everyone,

I am back with more questions, hope this reaches you well.

So from a Rest API extraction, I have been receiving such below JSON file.

[{"string":"{\"leads\":[{\"id\":\"cqzv70in4wrhhdu3\",\"cid\":\"cqzv70in4wrhhdu3\",
\"mid\":\"1430488\",\"email\":\"xxxxxxxxx\",
\"created_at\":\"2019-10-22T20:34:39.982Z\",\"updated_at\":\"2019-10-22T20:35:52.533Z\",
\"lead_score\":0,\"subscribed\":true,
\"dynamic_attributes\":{\"city\":\"Paris\",\"first_name\":\"xxxxxxxx\",
\"indiquez_votre_numéro_de_fixe_\/_mobile\":\"xxxxxxxx\",
\"je_souhaite_recevoir_des_informations_\\u0026_jeux_concours_des_parten\":false,
\"je_souhaite_recevoir_des_informations_\\u0026_jeux_concours_du_groupe_\":true,
\"last_name\":\"xxxxxxx\",\"pays\":\"France\", .........

I don't know how to parse this content and insert into my DB.

Thank you all for your help, millions times.

[Anonymous]

That looks like an escaped Java String representation of JSON within JSON. Your "string" fields value looks like....

"{\"leads\":[{\"id\":\"cqzv70in4wrhhdu3\",\"cid\":\"cqzv70in4wrhhdu3\",
\"mid\":\"1430488\",\"email\":\"xxxxxxxxx\",
\"created_at\":\"2019-10-22T20:34:39.982Z\",\"updated_at\":\"2019-10-22T20:35:52.533Z\",
\"lead_score\":0,\"subscribed\":true,
\"dynamic_attributes\":{\"city\":\"Paris\",\"first_name\":\"xxxxxxxx\",
\"indiquez_votre_numéro_de_fixe_\/_mobile\":\"xxxxxxxx\",
\"je_souhaite_recevoir_des_informations_\\u0026_jeux_concours_des_parten\":false,
\"je_souhaite_recevoir_des_informations_\\u0026_jeux_concours_du_groupe_\":true,
\"last_name\":\"xxxxxxx\",\"pays\":\"France\",........."

A way of dealing with this would be to use something like org.apache.commons.lang3.StringEscapeUtils to unescape this String and then use a tExtractJSONField component to extract the data from the unescaped String. You will need the Apache Commons-Lang3 Jar for this. Take a look here at the Javadocs....
https://commons.apache.org/proper/commons-lang/apidocs/index.html?org/apache/commons/lang3/StringEsc...