Hey everyone,
I am trying to use the JSON to Table node that is reading data from a website. The Get Request is working perfectly. However, I am getting the data in a table but is not useful since the column name for the value is not coming from the variable name.
This is an example of the JSON:
https://vpic.nhtsa.dot.gov/api/vehicles/decodevin/3GNDA13D76S000000*BA?format=json
In a more direct example, this is the data from the JSON:
{“Value”:“CHEVROLET”,“ValueId”:“467”,“Variable”:“Make”,“VariableId”:26}
I am getting a column for each as it is structured, but I would like to use the Variable as the column name for the value. For example, Skip the “Variable” column and use the value “MAKE” as the column name for “CHEVROLET”.
Any help will be appreciated!
Regards,
JAA
Hi @JAArroyo and welcome to the Knime Community.
Could you please give an example of how the output would look like for your example?
Are you saying that you want this:
{“Value”:“CHEVROLET”,“ValueId”:“467”,“Variable”:“Make”,“VariableId”:26}
to look like one column only, like this?
+-----------+
| MAKE |
+-----------+
| CHEVROLET |
| .... |
What about the ids? Ignore them?
bruno29a, thank you for responding.
Yes, exactly. I do not need the ids.
Hi @JAArroyo , ok no problem, thank you for confirming.
I put something together that can get this info, and it looks like this:
Result:
Here’s the workflow: JSON to table - Get MAKE value.knwf (16.3 KB)
3 Likes
Thank you so much. I believe that this workaround will work!
@bruno29a would you happen to know if there is a way to call the value based on the value of the object. I just noticed that the columns are not entirely correct. Continuing with the example of Make, if the object of Make is not available, the row will be filled with the next object.
This is what is happening.
is this related to your sample api ?
I can’t see missing data
ex.knwf (15.2 KB)
br
Hi @JAArroyo ,
That should not happen the way I built it. It will read everything, and then filter only where Variable is Make. If there is nothing, then it will not give you anything as there is nothing to filter. It will not return you “the next object”, not after the filter.
Also, based on your screenshot, this is not how my workflow is configured. It’s configured so that everything is processed vertically, meaning you should have only 1 Value column, 1 Variable column, with multiple rows (hence vertically - and that is why I can filter on Variable is Make). Your screenshot is showing that it has at least 75 Value columns and 75 Variable columns, which is a horizontal representation of the data.
Can you show me what you are doing and what’s the URL you used?
This is the workflow
The excel reader loads a file with a column containing multiple VINs. The URL is repeated in each row with the constant value column (https://vpic.nhtsa.dot.gov/api/vehicles/decodevin/VIN*BA?format=json). Then with the string manipulation I replace the VIN in the URL with the actual VIN from the loaded excel. The Get Request is performed then on each row with a different VIN. Finally, I use the JSON to Table and filter the columns I need to be written in a CSV. However, not every VIN contains all the objects. For example, if the “model” object is missing for one VIN, whatever is the next object will appear under that column.
Hi @JAArroyo , yes I understood how you are operating - it’s kind of the common way to do this, but it’s still not giving a URL that I can test/play with.
However, looking at your workflow, it’s not even close to what I did. It’s normal that you are not getting the same results, and also normal that you are extracting the data horizontally. What made my workflow to extract vertically was the Ungroup.
I’m not sure what you are expecting from your workflow, it just won’t work the way you want it to (I mean, that’s why you asked for help in the first place if I’m not mistaken).
1 Like
Where VIN will change based on the row.
Example: 3GNDA13D76S000000
Yes, this is working perfectly for one request. However, as the flow shown, I need to do this in multiple VINs. I am sure there must be a more efficient way.
