Repository navigation
Description
A checklist of items that I plan to tackle. Please feel free to edit and open issues related to a certain topic
udfs to support
-
variant_to_json- returns a JSON string from aVariantArray -
json_to_variant- returns aVariantArrayfrom a JSON string -
cast_to_variant- returns aVariantArrayfrom a column -
variant_get(VariantArray, path)- returns the extracted type dictated by thepathfrom theVariantArray -
is_variant_null(VariantArray)- tests whether elements inVariantArrayareVariant::Null -
variant_pretty(VariantArray)- returns a human-readable version ofVariantArray -
variant_schema(VariantArray)- returns the schema of a VariantArray -
variant_object_construct(key1, value1, [keyN, valueN]) -
variant_object_delete(VariantArray, VariantPath) -
variant_object_insert(key, value) -
variant_list_construct(value1, [valueN]) -
variant_list_insert(VariantArray, value) -
variant_list_delete -
variant_explode -
variant_explode_outer
Databricks supports the following variant-related udfs: https://docs.databricks.com/gcp/en/sql/language-manual/sql-ref-functions-builtin#variant-functions. I think it would be very cool to achieve 1:1 parity with their functionality
Apache spark's variant functions: https://spark.apache.org/docs/latest/api/sql/search.html?q=variant
misc
- Add a
examples/directory that lists examples for every udf we support, we can make use of a sample JSONL dataset like: https://www.nyc.gov/site/tlc/about/tlc-trip-record-data.page - Have integration tests, preferably using slt tests
- Add more unit tests
- Write documentation
- Write the README
- Have better error messages, especially when instructing users which arguments to pass Improve error messaging #14
- Pick a license Pick licensing #15
questions
It would also be good to compile a list of questions/ideas/limitations that arise from interfacing with arrow's parquet-variant libraries:
-
Why does
VariantArraynot implementIntoIter? -
variant_to_jsonshould accept an optional format configurations. Currently the library dictates how to map specialized Variant types to JSON (e.g. timestamps are always formatted as a string) -
There is a lot of ceremony to go from a
Variantto aVariantArray. MaybeVariantArrayshouldimpl From<IntoIterator<Item = Variant>>? -
When checking if 2
VariantArrays are equal, it is a bit odd that it will panic when calling.value(i)when the ith position has empty metadata and value? -
Why doesn't
VariantArrayimplPartialEq? -
Is there a reason why
VariantArraydoesn't implArray? Seems this would be super nice
Activity
Add more tests
alamb commentedon Oct 14, 2025
I agree this would be a great target to shoot for (maybe even put a note in the readme that the goal of this crate is to provide databricks / spark compatible variant functions)
The databricks list seems pretty similar to what is in spark: https://spark.apache.org/docs/latest/api/sql/index.html
alamb commentedon Oct 14, 2025
I recommend putting the examples in the crate readme / library home page as rustdocs to make sure they keep running and make them easier to discover
I recommend Apache 2.0 , given it is well known and is straightforward to apply / understand, and consistent with most other DataFusion libraries.
This is a great list! I think you are probably the first person to really try and integrate Variant so this feedback is super valuable.
These would be great tickets to file in arrow-rs and attach to the epic. Would you like me to do so? Or would you rather do so yourself?
I don't think there are great reasons for these rough edges, and I suspect we can make them all much easier
alamb commentedon Oct 14, 2025
There is also a list of functions in the original iceberg proposal here that might be good to reference:
friendlymatthew commentedon Nov 13, 2025
cc @XiangpengHao if you have any thoughts/ideas
XiangpengHao commentedon Nov 13, 2025
Thanks @friendlymatthew for looping me in! I'm working on this through the lens of json_bench: https://github.com/ClickHouse/JSONBench
Basically convert all json data to variant, and run the benchmark queries through
variant_get. I bet we can improve performance in several places.alamb commentedon Nov 14, 2025
I will be disappointed if you can't find several 2-3x improvements without looking hard.
As some added incentive, you want to turn your student army loose on this, I would be willing to offer "I'll take you to dinner in Madison" type prizes for any student who produces a merged PR that gives a 2x or better performance improvement in some important variant operation (like json --> variant)
friendlymatthew commentedon Nov 14, 2025
I wonder if this incentive is only for UW–Madison students and not for starving engineers in NYC
alamb commentedon Nov 17, 2025
I will extend the offer to any such engineers in NYC, on a case by case basis (I do not have unlimited budget 😆 )
alamb commentedon Dec 2, 2025
I found an interesting blog about variant here: https://www.e6data.com/blog/faster-json-sql-variant-data-type
Specifically, I think the description of
variant_getand the:and::operators are valuable and worth considering for this cratesunng87 commentedon Dec 3, 2025
I have been looking into these part too. Postgres has some operators defined for json data access https://www.postgresql.org/docs/18/functions-json.html#FUNCTIONS-JSON and these operators are already defined but not implemented in datafusion and sqlparser.
In postgres, the json path query is implemented as a UDF
jsonb_path_query. There is no operator for it.@?as a related one but only runs testing.:can be a good shortcut.::type casting is important here because we need to pass type coercion for those operators that can generate multiple data types of return value.By the way, just want to confirm is it possible to extend operators in an external library? I remember I saw custom planner implementation that rewrites operator into udf call.
alamb commentedon Dec 3, 2025
I am not sure -- if it isn't possible we should add it.
alamb commentedon Dec 3, 2025
BTW I think @Samyak2 wrote the blog about variant: https://www.e6data.com/blog/faster-json-sql-variant-data-type
Maybe they have some insights to share
Samyak2 commentedon Dec 3, 2025
Thanks for the mention @alamb! I would love to help with the effort here.
Regarding
:operator: databricks supports the operator for both JSON string and variant (ref). This makes it a lot easier to switch a column from string to variant - the changes needed in queries would be minimal. Snowflake also uses a similar syntax in their variant implementation (ref - note that this is not the same as the "open variant" type we're talking about here). Not that we need to use a proprietary engine as a reference, but they operate in the same analytics space and users would expect some familiarity in syntax.In practice too, I have noticed that users prefer colon over
variant_get(I don't have a source for this). Personally,column:field::intfeels much more natural overvariant_get(column, '$.field', 'int').alamb commentedon Dec 4, 2025
BTW @sunng87 I found that yes we can plan custom operators using an
ExprPlanner-- see here:https://github.com/apache/datafusion/blob/0f83c1d233499e80fd9b0baf89dd624099c1d1ba/datafusion/core/tests/user_defined/expr_planner.rs#L42-L41
Samyak2 commentedon Dec 4, 2025
Can we create a separate issue for colon
:operator? I want to take a stab at itSamyak2 commentedon Dec 4, 2025
One potential blocker I'm running into: I don't see a colon operator in https://docs.rs/sqlparser/latest/sqlparser/ast/enum.BinaryOperator.html
BinaryOperator::Custom(String)could work, but I don't see any usage of that in datafusion currently, so I'm guessing it needs a custom dialect implementation. The closest PR I could find is this - apache/datafusion-sqlparser-rs#533 (which was closed by the author for being an incorrect/incomplete implementation).Any ideas here? Should we start with adding colon operator to sqlparser's
GenericDialect? or should this be done in a custom dialect?alamb commentedon Dec 4, 2025
I concur -- the parser doesn't seem to support
:I would recommend finding an existing dialect that supports it (hopefully one does) and then we can add it to that specific dialect as well as GenericDialect
Samyak2 commentedon Dec 4, 2025
SnowflakeDialect seems to support it:
(in
datafusion-sqlparser-rs)gives this:
Update:
GenericDialectalso supports it - https://github.com/apache/datafusion-sqlparser-rs/blob/326f1118081807933160cb530e69dfdbf7a84b22/src/parser/mod.rs#L3759 . Butecho "select a:b;" | cargo run --example cli -- -doesn't work. Seems to be a precedence thing, because adding this fn toimpl Dialect for GenericDialectworks (copied from SnowflakeDialect):I'll try to get this into a proper PR.
GenericDialect: support colon operator for JsonAccess
Samyak2 commentedon Dec 4, 2025
I have a draft PR here to support
:in GenericDialect - apache/datafusion-sqlparser-rs#2124.A couple of tests are failing, but I want to know if my approach is correct.
GenericDialect: support colon operator for JsonAccess
GenericDialect: support colon operator for JsonAccess
Samyak2 commentedon Jan 1, 2026
I have created a separate issue for
:operator: #26BTW, the PR in parser is ready for review - apache/datafusion-sqlparser-rs#2124. Not sure who needs to be tagged there, so I'm putting it out here.
GenericDialect: support colon operator for JsonAccess
GenericDialect: support colon operator for JsonAccess
sdf-jkl commentedon Sep 24, 2026
superseded by #69