When reading a csv or json file, columns get sorted in the dataframe, how NOT to?

[keltia]

Describe the bug

Given a json (or a csv) read by datafusion-cli with

create external table t17 stored as json location '20251117.json';

whatever the columns order in the json(or csv) file, all columns get sorted in the dataframe, which means when writing into another format, order of columns will be different! I can not find the options either in the API or in datafusion-cli rto prevent this. I do not understand why this happens at all. At least there should be an option.

To Reproduce

head -1 20251117.json
{"uti":1763334319,"dat":"2025-11-16 23:05:19.186018808","tim":"23:05:19.186018808","hex":"4d00c3","fli":"","lat":48.20722961425781,"lon":2.240788386418269,"gda":"A","src":"A","alt":38000,"altg":37900,"hgt":-100,"spd":385,"cat":"A0","squ":"2256","vrt":0,"trk":56.84539,"mop":2,"lla":2,"tru":29,"dbm":-90,"nucp":8,"nic":8,"pic":11}

datafusion-cli

> create external table t17 stored as json location '20251117.json';
0 row(s) fetched.
Elapsed 0.024 seconds.

> describe t17;
+-------------+-----------+-------------+
| column_name | data_type | is_nullable |
+-------------+-----------+-------------+
| alt         | Int64     | YES         |
| altg        | Int64     | YES         |
| cat         | Utf8      | YES         |
| dat         | Utf8      | YES         |
| dbm         | Int64     | YES         |
| fli         | Utf8      | YES         |
| gda         | Utf8      | YES         |
| hex         | Utf8      | YES         |
| hgt         | Int64     | YES         |
| lat         | Float64   | YES         |
| lla         | Int64     | YES         |
| lon         | Float64   | YES         |
| mop         | Int64     | YES         |
| nic         | Int64     | YES         |
| nucp        | Int64     | YES         |
| pic         | Int64     | YES         |
| spd         | Int64     | YES         |
| squ         | Utf8      | YES         |
| src         | Utf8      | YES         |
| tim         | Utf8      | YES         |
| trk         | Float64   | YES         |
| tru         | Int64     | YES         |
| uti         | Int64     | YES         |
| vrt         | Int64     | YES         |
+-------------+-----------+-------------+
24 row(s) fetched.

there should NO SORTING by defaullt.

Expected behavior

Columns are not sorted.

Additional context

I have seen the file_sort_order option, but I do not believe this is it. I cannot find any option to reverse the behaviour.
I use the bdt tool every day to convert from JSONL to CSV and that's where I found the problem. bdt use datafusion for all its operations.

[keltia]

Do you have a "pola" tag? Because in essence, it is a POLA (Principle Of Least Astonishment) violation for me .

[2010YOUY01]

Do you have a "pola" tag? Because in essence, it is a POLA (Principle Of Least Astonishment) violation for me .

Today I learned. We don't have that yet, and honestly, the term “POLA” feels like it violates POLA itself.

[keltia]

I may just fork bdt to use polars instead, if this is not answered. polars does not have this reordering/sorting of columns issue.

[Jefffrey]

For JSONs, this does seem to be the expected behaviour of DataFusion (I don't think we have a config for this behaviour). As for why it happens, I assume it's because usually the order of keys in a JSON shouldn't be significant and ideally systems don't need to rely on the order of keys within JSON files to function. We could look into fixing this behaviour (though part of the schema resolution taking place here is located in arrow-rs) depending on the appetite for the fix, considering JSON is considered unordered 🤔

• I guess if polars does it then that's a point towards following suit and doing the fix, though not sure if they do it intentionally or it's just a happy side-effect of their implementation

For reference, the related code:

datafusion/datafusion/datasource-json/src/file_format.rs

Lines 215 to 229 in f1ecacc

	let schema = match r.payload {
	#[cfg(not(target_arch = "wasm32"))]
	GetResultPayload::File(file, _) => {
	let decoder = file_compression_type.convert_read(file)?;
	let mut reader = BufReader::new(decoder);
	let iter = ValueIter::new(&mut reader, None);
	infer_json_schema_from_iterator(iter.take_while(|_| take_while()))?
	}
	GetResultPayload::Stream(_) => {
	let data = r.bytes().await?;
	let decoder = file_compression_type.convert_read(data.reader())?;
	let mut reader = BufReader::new(decoder);
	let iter = ValueIter::new(&mut reader, None);
	infer_json_schema_from_iterator(iter.take_while(|_| take_while()))?
	}

Here when we call infer_json_schema_from_iterator() from arrow-rs:

pub fn infer_json_schema_from_iterator<I, V>(value_iter: I) -> Result<Schema, ArrowError>
where
    I: Iterator<Item = Result<V, ArrowError>>,
    V: Borrow<Value>,
{
    let mut field_types: HashMap<String, InferredType> = HashMap::new();

    for record in value_iter {
        match record?.borrow() {
            Value::Object(map) => {
                collect_field_types_from_object(&mut field_types, map)?;
            }
            value => {
                return Err(ArrowError::JsonError(format!(
                    "Expected JSON record to be an object, found {value:?}"
                )));
            }
        };
    }

    generate_schema(field_types)
}

• https://github.com/apache/arrow-rs/blob/ed9efe78e4cc958cc96707557818e754419debb0/arrow-json/src/reader/schema.rs#L500-L521

Specifically how we have field_types as a hashmap which I'm guessing is where the order of the columns is determined by the keys in there.

Though you mention that you see the same behaviour for CSVs but I don't think that's the case? Unless you have a reproduction? For reference my attempt:

datafusion-cli (main)$ cat /Users/jeffrey/Downloads/order.csv
zzz,bbb,column1,abc
1,2,3,4
1,2,3,4
1,2,3,4
datafusion-cli (main)$ cargo run
    Finished `dev` profile [unoptimized + debuginfo] target(s) in 0.47s
     Running `/Users/jeffrey/.cargo_target_cache/debug/datafusion-cli`
DataFusion CLI v51.0.0
> create external table t17 stored as csv location '/Users/jeffrey/Downloads/order.csv';
0 row(s) fetched.
Elapsed 0.061 seconds.

> select * from t17;
+-----+-----+---------+-----+
| zzz | bbb | column1 | abc |
+-----+-----+---------+-----+
| 1   | 2   | 3       | 4   |
| 1   | 2   | 3       | 4   |
| 1   | 2   | 3       | 4   |
+-----+-----+---------+-----+
3 row(s) fetched.
Elapsed 0.032 seconds.

> describe t17;
+-------------+-----------+-------------+
| column_name | data_type | is_nullable |
+-------------+-----------+-------------+
| zzz         | Int64     | YES         |
| bbb         | Int64     | YES         |
| column1     | Int64     | YES         |
| abc         | Int64     | YES         |
+-------------+-----------+-------------+
4 row(s) fetched.
Elapsed 0.004 seconds.