Zerobus Variant Data not Processed as Expected (json data destined for Variant column).

[DB1To3]

Lets say I have a table receiving zerobus data (json) over HTTP/rest.

That last VARIANT is "ValueJson".

If I want to submit data to it, I would expect to be able to use normal json formatting:

[
    {
        "a": "E",
        "b": 123,
        "c": "xyz", 
        "ValueJson": { "x" :  "y" }


    }
]
  

Unfortunately it doesn't want that part of the record to be ACTUAL json.  The error is like so

Record decoder/encoder error: invalid type: map, expected a string at line 13 column 24. Error Code: 4044, Error State: 3.

Unfortunately the fix is to send json-encoded json (escaped json in a string, since the error says it expected a string).

[
    {
        "a": "E",
        "b": 123,
        "c": "xyz", 
        "ValueJson": "{ \"x\" :  \"y\" }"


    }
]
  

Does this seem reasonable behavior, or is it a bug?  I think it is a bit unnatural to have to encode the json VARIANT instead of just nesting it inside the record as normal json.  Ideally the use of this VARIANT wouldn't force us towards using json-encoded-json.  It isn't the end of the world, but it seems like a very unfortunate way of sending data from a remote client - one that shouldn't need to have a deep understanding of the eccentricities of the VARIANT-parsing rules for zerobus.

[DB1To3]

I found docs that say I'm supposed to rely on json-encoded-json:

https://docs.databricks.com/aws/en/ingestion/zerobus-concepts#supported-data-types

That is pretty unfortunate.  I guess it isn't the end of the world, but it gives a dirty feeling when presenting data to a remote system in this way. 

[Jen_Evangelista · Accepted solution]

Agree it feels unnatural, but you can hide it from the remote client with a thin encoding step right before sending, so producers keep working with normal nested JSON.

A small helper that encodes only the VARIANT columns:

import json

VARIANT_COLUMNS = {"ValueJson"}

def to_zerobus_record(record: dict) -> dict:
return {
k: json.dumps(v) if k in VARIANT_COLUMNS and not isinstance(v, str) else v
for k, v in record.items()
}

record = {"a": "E", "b": 123, "c": "xyz", "ValueJson": {"x": "y"}}
payload = [to_zerobus_record(record)]
# -> [{"a": "E", "b": 123, "c": "xyz", "ValueJson": "{\"x\": \"y\"}"}]

On the Databricks side nothing changes: it lands as a real VARIANT, not a string, so you query it normally:

SELECT
a,
ValueJson:x::string AS x,
variant_get(ValueJson, '$.x', 'string') AS x_alt
FROM catalog.schema.your_table;

If several producers send data, ship this helper in a small shared client package so nobody needs to know about the rule.

[DB1To3]

Thanks for the tip.  Have a nice weekend.