Shape / columns · All operations

Write a nested value as text

A variant of Parse nested values

Turn a struct, list or JSON value back into a JSON text string.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Write a struct, a list and a JSON value as JSON text

SQL
CREATE TEMP TABLE nested AS SELECT * FROM (VALUES
  (1, {'channel': 'web', 'n': 2},   [1, 2, 3],         '{"a": [1,  2]}'::JSON),
  (2, {'channel': NULL,  'n': 0},   []::INTEGER[],     '{"a": null}'::JSON),
  (3, NULL,                         NULL,              NULL)
) t(id, s, l, j);
CREATE TEMP TABLE result AS
SELECT id, to_json(s)::VARCHAR AS s_text, to_json(l)::VARCHAR AS l_text, to_json(j)::VARCHAR AS j_text FROM nested;
SELECT * FROM result ORDER BY id;
SELECT CASE WHEN ((SELECT [s_text, l_text, j_text] FROM result WHERE id = 1) = ['{"channel":"web","n":2}', '[1,2,3]', '{"a":[1,2]}'] AND (SELECT [s_text, l_text, j_text] FROM result WHERE id = 2) = ['{"channel":null,"n":0}', '[]', '{"a":null}'] AND (SELECT count(*) FROM result WHERE id = 3 AND s_text IS NULL AND l_text IS NULL AND j_text IS NULL) = 1) THEN 'as expected' ELSE error('the struct, the list and the JSON value were not written as JSON text, or a missing value did not stay missing') END AS verdict;
  • The statement builds its own data.
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the struct, the list and the JSON value were not written as JSON text, or a missing value did not stay missing”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Azure Data Factory (1)
  • Stringifyread 2026-09-27

    Turn complex-typed columns into plain strings

Duckle (1)
Power Query M (1)
  • Json.FromValueread 2026-09-27

    Serialise a value to JSON as binary in a given text encoding

Background

Serialisation translates a data structure into a format that can be stored or transmitted and reconstructed later.[1] In DuckDB, to_json creates JavaScript Object Notation (JSON) from a value of any type, turning a LIST into a JSON array and a STRUCT or MAP into a JSON object.[2]

When to use it

In DuckDB, any type can be cast to JSON, so a DATE such as 2023-05-12 becomes the JSON string "2023-05-12".[3] Because JSON has no native temporal types, DuckDB's json_structure reports DATE, TIME and TIMESTAMP values as VARCHAR, and fed back into from_json, that structure produces them as VARCHAR.[4]

A JSON number such as 1E400 or 3.141592653589793238462643383279 may indicate potential interoperability problems, since JSON implementations may limit the range and precision they accept.[5] Write high-precision decimals and long identifiers as JSON strings when the parser that will read them is unknown. (Meridian's view) DuckDB's json_normalize returns JSON with the keys of every object sorted recursively, so documents that differ only in key order give the same md5 hash.[4] Normalise before hashing or diffing serialised values, so a change in key order is not mistaken for a change in content. (Meridian's view)

The method

DuckDB's to_json has the aliases json_quote, array_to_json, which only accepts a LIST, and row_to_json, which only accepts a STRUCT.[2] DuckDB's json function parses and minifies JSON, and json_pretty returns it in an indented format.[4] In DuckDB, whitespace in JSON text is kept in round trips, because the data is stored physically as a VARCHAR.[3] Serialisation is also called marshalling in some situations, and the opposite operation, extracting a data structure from a series of bytes, is deserialisation.[1]

Sources

  1. [1]Serialization - Wikipediasecondary source
  2. [2]Creating JSON – DuckDBdocumentation
  3. [3]JSON Type – DuckDBdocumentation
  4. [4]JSON Processing Functions – DuckDBdocumentation
  5. [5]RFC 8259: The JavaScript Object Notation (JSON) Data Interchange Formatstandard