JSON Query errors in Pipelines - null and other issues

Not really a question (unless someone has a better approach), but just posting to help others who may be dealing with what I just spent hours trying to figure out.

BACKGROUND: I created a pipeline to send data from created records in a table over to an external cloud database (xata, in my case) for other purposes. I created data tables and schema in Xata that would match my QB table. Xata can create new records there using an API call via JSON. In the pipeline, the trigger was the creation of a QB record, followed by JSON query (Make Request action). I chose to do a webhook via pipelines (via Make Request) rather than webhooks in the QB table because I couldn't get the webhook there to work either and webhooks there provided almost no error feedback.

ISSUE: The Make Request action, i.e. the JSON POST query kept failing. Wouldn't even send the query. After much troubleshooting, here's what I found. After much troubleshooting, it seemed the issues were:

• Null values - JSON can't deal with null values easily, apparently
• Data type conflicts (this came up after I got the webhook to send)

SOLUTION:

1. Make sure you take advantage of pipedream.com. That tool allows you to give your webhook in pipedream.com where you can then examine the details of the URL sent to the endpoint. Otherwise, you're stuck only able to look at the response from your actual endpoint, which isn't always helpful.
2. JSON apparently can't deal with null values for boolean and numeric fields. In other words, if you're trying to pass a numeric field that has no value in it, i.e. null, then QB's webhook just puts a blank there, which causes a JSON error.  Example, if the QB field is "Age" as a numeric field, and you have this JSON body:
   

   {
   "Age_otherDB": {{a.age}}
   }

   
   and the value in the record is null, i.e. you haven't put a value there yet, the output is:
   

   {
   "Age_otherDB": ,
   }

   and apparently JSON can't deal with that. What JSON needs is "Age_otherDB": null
3. One option is to have Condition actions in the pipeline that would run different Make Request actions that omit the null fields from their requests, but the solution I used uses some Jinja code (QB's Jinja help page)  in the QB webhook body to make the value 'null' if it's empty:
   

    "Age_otherDB": {{a.age|to_json}},

   The 'to_json' bit is a filter of some type in jinja that makes it so that if the value is null, instead of a blank, it'll put 'null', so that the output is: "Age_otherDB": null

4. Another issue I had was that my external Xata database had fields I had created as Boolean (true/false), which were checkbox fields in Quickbase which have values of "Yes/No" when exported. I could have converted the Xata fields to Text fields to receive the "Yes/No" values, but since I was learning about jinja, I chose to use jinja code to "convert" the values using jinja conditional statements. In this example, it's a QB field named "urgent" that was a checkbox:
   

       {% if a.urgent == "Yes" %}"Urgent_otherDB": true{% elif a.urgent == "No" %}"Urgent_otherDB": false{% else %}"Urgent_otherDB": null{% endif %},

5. I also used jinja to help me with yet another field that the external Xata database had as field type of "email", but which was a text field in QB. When the field in QB was blank, and tried to pass to Xata a text value of "", the JSON query went through, but the Xata API failed because it was its email field was either an email address or it had to be null. So I used the similar jinja conditions to pass value of 'null' if blank:
   

   {% if a.email is not none %} "Email_otherDB": "{{a.email}}" {% else %}"Email_otherDB": null{% endif %}

So, there you have it. In summary, when passing a JSON webhook in pipelines (maybe in tables webhooks too, not sure):

• Be sure to use the 'to_json' jinja thingy when passing the value of a numerical field, e.g. {{a.my_field|to_json}} so that it passes 'null' when the field is empty.
• Use the jinja conditional and boolean functions (read this help doc) to reformat fields to what your receiving API needs. 

As I write this, I realize that Pipelines probably has actions that can do the conversions I mention above, but oh well, I learned something new about jinja. So there you go.