Re: Emitting JSON to file using COPY TO

On 2026-03-18 We 9:58 PM, jian he wrote:
> On Wed, Mar 18, 2026 at 10:37 PM Daniel Verite <daniel@manitou-mail.org> wrote:
>> Currently there's no difference in output between the null
>> json value and the SQL null.
>>
>> postgres=# create table tbl  (j jsonb);
>> postgres=# insert into tbl values('null');
>> postgres=# insert into tbl values(null);
>> postgres=# copy tbl to stdout with (format json);
>> {"j":null}
>> {"j":null}
>>
>> Does it have to be that way or are there valid distinct outputs
>> that  we could use to avoid this ambiguity?
>>
> This is an existing (quite old) behavior of
> composite_to_json->datum_to_json_internal, IMHO.
>
> ```
>      if (is_null)
>      {
>          appendBinaryStringInfo(result, "null", strlen("null"));
>          return;
>      }
> ```
> produce the same results as
> ```
> case JSONTYPE_JSON:
>      /* JSON and JSONB output will already be escaped */
>      outputstr = OidOutputFunctionCall(outfuncoid, val);
>      appendStringInfoString(result, outputstr);
>      pfree(outputstr);
>      break;
> ```
>
> Therefore I intended to document it as below:
>
>    <refsect2 id="sql-copy-json-format" xreflabel="JSON Format">
>     <title>JSON Format</title>
>      <para>
>        When the <literal>json</literal> format is used, data is
> exported with one JSON object per line,
>        where each line corresponds to a single record.
>        The <literal>json</literal> format has no standard way to
> distinguish between an SQL <literal>NULL</literal> and a JSON
> <literal>null</literal> literal.
>        In the examples that follow, the following table containing JSON
> data will be used:
> <programlisting>
> CREATE TABLE my_test (a jsonb, b int);
> INSERT INTO my_test VALUES ('null', 1), (NULL, 1);
> </programlisting>
>
>      When exporting this table using the <literal>json</literal> format:
> <programlisting>
> COPY my_test TO STDOUT (FORMAT JSON);
> </programlisting>
>      In the resulting output, both the SQL <literal>NULL</literal> and
> the JSON <literal>null</literal> are rendered identically:
> <screen>
> {"a":null,"b":1}
> {"a":null,"b":1}
> </screen>
>     </para>
>    </refsect2>
>
>
>
> what do you think?
>
>
>

I can live with that, if others can.


cheers


andrew


--
Andrew Dunstan
EDB: https://www.enterprisedb.com