request to add typed text/csv to registry, in parity with major databases & languages

[coolaj86]

Rationale

A variety of languages support CSV as a first-class citizen for objects - Swift and PowerShell come immediately to mind, but many (perhaps most) languages support typed object serializer/deserializer for CSV through 3rd party libraries or simple map operations as well - Go, JavaScript w/ JSDoc, Python, etc.

Since CSV is much simpler, smaller, and less server intensive that JSON - and even many binary formats - for representing many kinds of typed objects, it's often the ideal choice for easy-to-cache, easy-to-update data.

Creating complex permutations in a database which explodes the size of data that needs to be loaded in RAM, then transmitted to the application, and then across the Internet just to satisfy a frontend rendering contract is a little crazy when you stop and think about it - it's trivial to iterate over and create in-memory graphs client-side (whether Web or iOS, etc).

If you use CSV, however, you can write very simple queries that satisfy business logic, often at 1/5 or 1/10 the size before gzip - which reduces stress across the entire pipeline.

Not only that, but when API endpoints are exposed as CSV they can be integrated very easily with Excel and Google Sheets, which drastically increases user productivity and drives down development cost and time, depending on the data.

Typical CSV Hydration

In general, there are 3 ways in which CSV hydration is performed, which parallel how Postgres, MariaDB, etc store and hydrate data:

1. Standard RFC

id,name,dob,ssn
1,John Smith,4/1/1969,12-34-5678
2,Jane Doe,5/5/1971,10-20-3456

• fields separated by commas - or an alternate character, such as TAB or US (ASCII Unit Separator)
• records separated by newlines - or an alternate character, such as RS (ASCII Record Separator)
• values containing quotes, field, or record separators are quoted
  (where a double double quote "" escapes to a single quote)
  (it's less common for the quote character to be replaced, but sometimes ` is used)
• empty lines are ignored, or treated as comments
• lines beginning with a prescribed comment character (usually #) are ignored, or treated as comments
• most parsers do NOT allow sequences of characters (i.e. //) as a separator or comment, except for \r\n
• query parameters may be used to choose delimiters

In this configuration fields map exactly to a typical JSON object - except that:

• strings, booleans, numbers, and nulls require extra information to distinguish (which OpenAPI already provides)
• (optional) the CSV header format may different than the object key format
  • use index rather than name
  • map name to a different form of the name

Just for the purpose of illustration, here's a schema with csv_field_name and csv_field_index (CSV is 1-indexed):

components:
  schemas:
    Thingy:
      type: object
      properties:
        id: { type: integer, format: uint32, csv_field_name: "I.D." }
        slug: { type: string, csv_field_index: 2 }
        name: { type: string, csv_field_name: "Name" }
        is_unique: { type: boolean, csv_field_name: "Is Unique" }
        created_at: { type: string, format: date-time }
        updated_at: { type: string, format: date-time }

I realize that's probably not exactly where this belongs (probably with something related to the text/csv registration), but it's just to show the idea.

note: query parameters may be used to choose delimiters

2. RFC, but with Lists

id,name,friends,ssn
1,John Smith,"2,3",12-34-5678
2,Jane Doe,"1,3",10-20-3456
3,Bob McMaster,"1,2",44-33-5555

Another common format is simply to embed lists - so perhaps using \t as the record separator, but using a space as a subrecord separator.
(this is also how OIDC scopes and many other simple list types uses spaces to create a list in query params)

This is similar to Array column types in a database.

For example, that might be represented something like this with a list_delimiter:

components:
  schemas:
    Thingy:
      type: object
      properties:
        # ...
        codes:
          type: array
          list_delimiter: \t
          items:
            type: integer
            format: int32

And, not that I think that metadata belongs there, but just for illustration of the idea.

3. Nested JSON

id,name,options,ssn
1,John Smith,"{""foo"":""bar"",""n"":42}",12-34-5678
2,Jane Doe,"{""baz"":""qux"",""n"":37}",10-20-3456

Just as with a JSON column in a database, occasionally you need something free-form that can hold awkward or inconsistent information without adding a dozen fields that might typically be empty.

components:
  schemas:
    Thingy:
      type: object
      properties:
        # ...
        options:
          nested_json: true
          $ref: '#/components/schemas/Options'
    Options:
      type: object
      properties:
        color:
          type: string
          enum: [red, green, blue]
        size:
          type: integer
          minimum: 1
      required: [color]

All of the same quoting rules apply - the implementor simply needs to know that a field should be parsed as JSON as opposed to just so happening to start and end with a [ and ] or { and }.

Bonus: Base64 & Hex, like JSON

id,name,icon
1,John Smith,"data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACH5BAEAAAAALAAAAAABAAEAAAICRAEAOw=="
2,Jane Doe,"data:image/svg+xml,<svg xmlns='http://www.w3.org/2000/svg' width='1' height='1'/>"

It's pretty common to use data URLs or raw Base64 or Hex to encode binary data. This is no different from how it would already be handled in JSON.

Super Bonus: Multiplexed

This s a bit out there, but there's also this idea of allowing multiple data-types in a single CSV that's been floating around - but I don't know of any actual implementations yet:

type,person,id,name
type,book,id,title,author
person,1,John Smith
book,1,The Art of Computer Programming,Donald E. Knuth

The theory is that you can sort the data before sending it downstream such that all relevant data to an object graph can be frontloaded, and UI can begin rendering while streaming.

However, in my own testing of a similar concept, I don't think there's any significant savings here - in fact it may be worse.

10s of thousands of gzipped records can be loaded in parallel via CSV endpoints and reconstructed in a graph on the client in a matter of dozens of milliseconds.

The extra work to reform the data beyond what a database query ORDER BY already does might even slow it down.

Considering how easily the data is cached and how easily just updated records can be gathered - most of the time of which is waiting for the query to finish in the DB - it might be a mistake to go this far.

Other considerations

These types would be useful for both GETing data to render on or list in a client as well as POSTing bulk data (such as something a user copied from a spreadsheet, or where a list of items need some properties adjusted).

Having first-class CSV support in OpenAPI 3 will make it easier to document, build, and support simpler, data-efficient APIs (and maybe even encourage them).

[karenetheridge]

I'll copy (with improvements) some comments I made in

• OpenAPI 3.0 does not support csv-serialized form-data arrays OpenAPI-Specification#2018

Here's how you could use text/csv in an OpenAPI document (caveat emptor: this is untested, so any errors are left as an exercise for the reader):

for 3.1+:

components:
  schemas:
    foo_as_object:
      type: object
      required: [ fieldname1, fieldname2, fieldname3 ]
      properties:
        fieldname1:
          type: string
        fieldname2:
          type: string
        fieldname3:
          type: string
    foo_as_csv:
      type: array
      prefixItems:
        - $comment: the header line; deserializes into a list of field names
          type: array
          const: [ fieldname1, fieldname2, fieldname3 ]
      items:
        $comment: the content lines; deserializes into a list of field values
        type: array
        prefixItems:
          - $ref: '#/components/schemas/foo_as_object/properties/fieldname1'
          - $ref: '#/components/schemas/foo_as_object/properties/fieldname2'
          - $ref: '#/components/schemas/foo_as_object/properties/fieldname3'
        minItems: 3
        maxItems: 3
responses:
  foo_in_response:
    content:
      application/x-ndjson:
        schema:
          type: array
          items:
            $ref: '#/components/schemas/foo_as_object'
        examples:
          'simple example':
            dataValue:
              - { fieldname1: foo, fieldname2: bar, fieldname3: baz }
              - { fieldname1: alpha, fieldname2: beta, fieldname3: gamma }
            serializedValue: |-
[
  { "fieldname1":"foo","fieldname2":"bar","fieldname3":"baz" },
  { "fieldname1":"alpha","fieldname2":"beta","fieldname3":"gamma" }
]
      text/csv:
        schema:
          $ref: '#/components/schemas/foo_as_csv'
        examples:
          'simple example':
            dataValue: [ [ fieldname1, fieldname2, fieldname3 ], [ foo, bar, baz ], [ alpha, beta, gamma ]
            serializedValue: |-
fieldname1,fieldname2,fieldname3
foo,bar,baz
alpha,beta,gamma

For 3.0 documents, "prefixItems" and "items" must be combined under "items" with an "anyOf" wrapper.

Note that in 3.2, you can also use either of these media types in a streaming response, e.g.:

content:
  application/json-seq:
    itemSchema:
      $ref:  '#/components/schemas/foo_as_object'
  text/csv:
    itemSchema:
      anyOf:
        - $ref:  '#/components/schemas/foo_as_csv/prefixItems/0'
        - $ref:  '#/components/schemas/foo_as_csv/items'

(side note: maybe we should add prefixItemSchema to better enhance streaming support?)

[handrews]

Longer response forthcoming, but to address this from @karenetheridge

(side note: maybe we should add prefixItemSchema to better enhance streaming support?)

IIRC, we didn't because you can use schema with prefixItems and then use itemSchema for the streaming part. But now that you mention it, we don't give any guidance that you can do this (and not wait for the whole stream to be done before validating the prefixItems in schema), and perhaps heuristically inspecting schema is not ideal. But that's why we didn't add it at the time.

[handrews]

@coolaj86 thanks for this really in-depth set of use cases! There may end up being several things to do here.

I have some general comments here to explain how this media type registration thing works as it is new. I mention several possible complications and limitations here; that doesn't mean we can't work around them or figure out ways in which they are not actually a problem. I'm just setting the stage as this is the first time we've discussed adding a media type since we created the registry.

---

We tend to separate the data model, which is what the Schema Object works with, from the serialization format which is what the media type determines. See OAS v3.2 §4.24.4 Parsing and Serializing for details, and the Example Object with the new dataValue and serializedValue fields for examples. The concepts (but not the new fields) apply to earlier versions as well.

This means that we can define the same Schema Object conventions for all media with the same data model, which we have already done for the various streaming JSON media types. In this case, text/csv and text/tab-separated-values have the same data model but different serialization. Other choices of field and record separators might not have a standard media type, but we can define one if needed and put it in our own registry even if it is not IANA-registered (e.g. application/jsonl and text/event-stream are not IANA-registered).

---

We try to stick with modeling the media type, rather than modeling any particular usage of the media type. So @karenetheridge's example works with text/csv as an array of arrays because that is what the media type is. It does not define special concepts for row or column headers as field or record names. There's more to consider here, but if we want to model things as named records, it might be better to define a media type that explicitly specifies that behavior.

---

Embedding JSON (or any other media type) is straightforward in OAS 3.1+, using contentMediaType and contentSchema. It can't really be done in OAS 3.0. The only mechanism available would be the Encoding Object, but using it would be challenging both because it is rather awkward and because the OAS text does not allow for it to be used outside of form media types.

In general, it is very hard to do complex things with OAS 3.0. If you can push to move to 3.1 (which makes moving to 3.2 trivial), you will be in much better shape. It's possible that a 3.0 implementation might add support for a media type from our registry if you lobby them, but there's no requirement to do so. And things like embedding JSON in a cell are just impossible in 3.0.