I'm extracting data nightly from JSON services of which have no validation. These datasets have over 100 columns. Is there way to import a schema into the attribute validator so for every column it would test data type and max length? It would take forever to test data type, structure and max length for every column in every dataset, but my fme processes keep failing.
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
- 7843 replies
Hi @rclarknxf, I think the solution would be different depending on how the desired schema (column names, data type for each column, max length for each column, and other restrictions if needed) is given. Could you please elaborate about this? It would be ideal if you could post a sample JSON document and the schema definition (maybe an external table?).
Hi @rclarknxf, I think the solution would be different depending on how the desired schema (column names, data type for each column, max length for each column, and other restrictions if needed) is given. Could you please elaborate about this? It would be ideal if you could post a sample JSON document and the schema definition (maybe an external table?).
{"success":true,"message":null,"result":{"pageNum":1,"numPages":31,"records":[{"NAME":"VALUE","NAME":"VALUE","NAME":"VALUE","NAME":"VALUE",.....}]}}
Ideally I would like to have a transformer that works for anything. not sure if the solution needs to be different for each. I used the attribute manager to make then name of the input columns the same as output so all it needs to do is say for X value can I be inserted into this column successfully. So for string - is length of value able to fit into column? For numeric does precision/scale of value fit into column? The writers have the schema (as in the definition of the column).
- 7843 replies
Hi @rclarknxf, I think the solution would be different depending on how the desired schema (column names, data type for each column, max length for each column, and other restrictions if needed) is given. Could you please elaborate about this? It would be ideal if you could post a sample JSON document and the schema definition (maybe an external table?).
- 7843 replies
Hi @rclarknxf, I think the solution would be different depending on how the desired schema (column names, data type for each column, max length for each column, and other restrictions if needed) is given. Could you please elaborate about this? It would be ideal if you could post a sample JSON document and the schema definition (maybe an external table?).
{
"success" : true,
"message" : null,
"result" : {
"pageNum" : 1,
"numPages" : 31,
"records" : [
{
"NAME1" : "VALUE1",
"NAME2" : "VALUE2",
"NAME3" : "VALUE3",
"NAME4" : "VALUE4"
}
]
}
}
- JSON Query: json["result"]["records"][*]
Hi @rclarknxf, I think the solution would be different depending on how the desired schema (column names, data type for each column, max length for each column, and other restrictions if needed) is given. Could you please elaborate about this? It would be ideal if you could post a sample JSON document and the schema definition (maybe an external table?).
- 7843 replies
Hi @rclarknxf, I think the solution would be different depending on how the desired schema (column names, data type for each column, max length for each column, and other restrictions if needed) is given. Could you please elaborate about this? It would be ideal if you could post a sample JSON document and the schema definition (maybe an external table?).
Hi @rclarknxf, I think the solution would be different depending on how the desired schema (column names, data type for each column, max length for each column, and other restrictions if needed) is given. Could you please elaborate about this? It would be ideal if you could post a sample JSON document and the schema definition (maybe an external table?).
- 7843 replies
Hi @rclarknxf, I think the solution would be different depending on how the desired schema (column names, data type for each column, max length for each column, and other restrictions if needed) is given. Could you please elaborate about this? It would be ideal if you could post a sample JSON document and the schema definition (maybe an external table?).
If the output dataset exists already, you can read the schema from the dataset with the Schema reader and validate the attributes of each record based on the schema definition.
If the output dataset doesn't exist yet, there is no way to get the schema definition at run-time unless you have a resource (e.g. an external lookup table) that defines the destination schema.
In any case, Python scripting may be necessary to validate the attributes for each record extracted from the JSON document.