API behavior for empty fields / null values

[Joshua_Deeden]

It appears that the record objects returned from the API are dropping any fields that do not have a value. Except in the case of linked table fields, then the field is there but the value is an empty array. This behavior is unexpected for me and I have a few questions…

1. Is the behavior I described in fact the existing/intended behavior? Or am I missing something?
2. If so, then this is unexpected for me because:

• Empty values are being represented inconsistently based on field type. Why represent a null text field as a field that doesn’t exist, and a null linked records field as an empty collection?

• I would expect to see fields that I know to be in my table to be faithfully represented in the API, even when those fields have null values.

• I have to inspect every single record to see what fields it has before I can reliably map it onto a model object. This is less than ideal for several (obvious?) reasons.

I noticed this behavior when using the Ruby client, but curl produces the same result.

[jvhellemond]

Anything from Airtable on this?

[Lincoln_Swaine-]

Why is this the way this works? I would even settle for a good way to get the field names for the whole table so I can complete the records myself.

[Bill_French]

@jvhellemond,

I’m quite experienced with Airtable’s API but the lack of values for every field in each JSON response did not come as a surprise to me. This is a usual and customary way that APIs are designed. Any given JSON payload response with fields that contain no data (i.e., null values) is unnecessary and wasteful. Furthermore, the value of NULL and empty are two different ideas.

ElasticSearch, for example, works exactly the same way and I think almost every NoSQL database API will do this.

The way our integration architectures should deal with this is to recognize that some records will not contain certain field entries, whereas, there are cases where the entry is literally an empty string. After all, a null value is not the same as an empty string value.

Ideally, you should write your code to accommodate missing field values and should you want to build an integration that learns about new fields and adjusts appropriately/dynamically, the lack of a fields() method makes this particularly challenging.

One way to overcome this (in Airtable) is to create a single hidden record that has every field populated and query that record to enumerate all fields. I’m not aware of any better approach but I’d sure like to find one. :winking_face:

[Lincoln_Swaine-]

I agree that we should distinguish between NULL and empty, but the way Airtable handles this (to my knowledge) doesn’t allow for that either! I don’t believe there’s a way to distinguish between the two when inputting values, and a record with an “empty” field value does not send anything for that field–not an empty string value. I’d argue that the behavior should instead be to send an empty string.

Short of that though, a fields method is imperative. An example: a field doesn’t need to have any values to have semantic importance. But (unless one uses a hack like yours–which, by the way, is a good idea, so thank you!) there’d be no way to know of its existence through the API. Surely Airtable maintains information about a table’s fields, so why wouldn’t they expose it?

[Bill_French]

@Lincoln_Swaine-Moore,

I’d argue that the behavior should instead be to send an empty string.

Agree(strikethrough) Correction. Disagree.

A field that is unused (i.e., one that has never had a value stored into it) should not be conveyed via the API as an empty string because it suggests something or someone actually did something to the field/record. Since this is [possibly] not the case when a record is partially populated it would be misleading to developers if every result set contained every field - even the ones that had never been set to a value.

As a developer, I often want to know how three consecutive workflow steps have impacted a record. Ergo, step 1 creates the record and populates certain fields. Null (or missing) fields indicate the state of the process. This is critical information when creating a state machine that now needs to perform a process at step 2, and step 3.

A JSON payload lacking a field that is in the schema is considered a NULL value (I think). At the very worst it should be absent from the result, and at best, it should be exposed in the result set with a NULL value. If – through an API call – you had set the value to an empty string, it should return an empty string. But it doesn’t appear that the Airtable UI is capable of editing a field into a state where it contains an empty string value (i.e., any field that has had a value that is then deleted, seems to revert to a NULL value). Despite the fact that the UI is not able to store empty strings, I suspect the API is capable of this, but I have not tested it.

Someone above mentioned consistency - I agree - it would be fine to expose every field with NULL values and especially so if a fields() method or schema API is not available.

… a field doesn’t need to have any values to have semantic importance.

Eloquent; totally agree! ergo - ideally, we need a schema API. Tricks like mine are not the way to do this.

Surely Airtable maintains information about a table’s fields, so why wouldn’t they expose it?

Indeed; the same logic should apply to blocks.