Summary
When building a connector, the user wants to extract specific fields (status and type) from a nested JSON response and put them in separate columns. They are facing issues with the transformation step affecting the extraction process.
Question
Hi everybody, I’m trying to build a connector, one of the fields (status) in the response is a list of fields from which I want to extract 2 fields (including status & type) to bring it in my table.
→ In the example below, I would like to extract the status & the type from the status container
"status": 200,
"body": {
"tasks": [
{
"id": "86by328yq",
"custom_id": null,
"custom_item_id": 0,
"name": "Ou avoir un filtre dans la recherche avancé",
"text_content": "",
"description": "",
"status": {
"status": "to do",
"color": "#87909e",
"type": "open",
"orderindex": 0```
Is there a way for the transformation step to extract 2 fields and put them in 2 different columns ?
It looks like the 1st transfo is avoiding the 2nd one to work properly, and the original response have disappeared
<br>
---
This topic has been created from a Slack thread to give it more visibility.
It will be on Read-Only mode here. [Click here](https://airbytehq.slack.com/archives/C027KKE4BCZ/p1712738220601839) if you want to access the original thread.
[Join the conversation on Slack](https://slack.airbyte.com)
<sub>
["extracting-fields", "nested-json-response", "transformation-step", "connector"]
</sub>
My hunch is that the first one is overriding the further up status field, essentially transforming the record into
"status": 200,
"body": {
"tasks": [
{
"id": "86by328yq",
"custom_id": null,
"custom_item_id": 0,
"name": "Ou avoir un filtre dans la recherche avancé",
"text_content": "",
"description": "",
"status": "to_do",```
before the second transformation comes, so that the nested `to_do` field no longer exists. Note that this is record transformation, not just a mapping to a different output.
Does it work if you swap them? If you swapped them I would imagine it would transform something like
```{
"status": 200,
"body": {
"tasks": [
{
"id": "86by328yq",
"custom_id": null,
"custom_item_id": 0,
"name": "Ou avoir un filtre dans la recherche avancé",
"text_content": "",
"description": "",
"status": {
"status": "to do",
"color": "#87909e",
"type": "open",
"orderindex": 0 ...
"type": "open", ```
then
```{
"status": 200,
"body": {
"tasks": [
{
"id": "86by328yq",
"custom_id": null,
"custom_item_id": 0,
"name": "Ou avoir un filtre dans la recherche avancé",
"text_content": "",
"description": "",
"status": "to_do",
"type": "open", ```
Alternatively writing the status.status to a different key that doesn't already exist in the record might do it as well.
Alternatively, you can do AddField transformations first, and then DeleteField status.
• AddField status_status as status.status,
• AddField status_type as status.type
• DeleteField status.
Something like this?
Not sure I understand what you mean by swapping the fields ?
To me it just look like when I transform a field, it overrides it before the 2nd action so the 2nd action does not find it.
So it looks like it’s not really “adding a field” but more like “replacing the original field by the selection”
In this example : {{record[“status”]}} is replaced by {{record[“status”][“status”]}} which prevent me to find the {{record[“status”][“type”]}} in the second step because it does not exist anymore
Yes, it’s working exactly as you’re intuiting it, though you’ve run into an edge case we should be clearer about. Maybe think about it as “MapValueToKey” (adding the field if it does not exist or overriding it if it does, similar to how a dictionary would work)
Therefore if you want to pull something from the value that is already there, you can either
• Pull the type nested value from the status key first and place it at a new key, then override the status key with the nested status value (my suggestion, via re-ordering the transformations)
• Pull the nested status and type values both into new keys, then remove the old key to stop passing any nested stuff you don’t want to emit (Natik’s suggestion via add and delete fields)
Ah yes I got you ! And it work well by changing the order of the fields I’m pulling now
Awesome, glad you got it figured out!