How to stringify PostgreSQL JSON data?

April 28, 2022

I have seen a lot of questions similar to this (e.g. this one) but couldn’t find one that helps. I’m looking for the equivalent of Javascript’s JSON.stringify() but for PostgreSQL.

E.g.:

db=# \pset null
Null display is "(null)".
db=# create table x(c1 json);
CREATE TABLE
db=# insert into x (c1) values (null), ('"blah"'::json), ('{"a":1}'::json);
INSERT 0 3
db=# select * from x;
   c1
---------
 (null)
 "blah"
 {"a":1}
(3 rows)

db=# select '''' || c1::text || '''' from x;
 ?column?
-----------
 (null)     --- this is wrong, should be 'null'
 '"blah"'
 '{"a":1}'
(3 rows)

db=# select '''' || (c1 #>> '{}') || '''' from x;
 ?column?
-----------
 (null)     --- this is wrong, should be 'null'
 'blah'     --- this is wrong, should be '"blah"'
 '{"a":1}'
(3 rows)

db=# select '''' || case when c1 is null then 'null' else c1::text end || '''' from x;
 ?column?
-----------
 'null'
 '"blah"'
 '{"a":1}'
(3 rows)

Can the last example be done without a CASE...END clause? And is it always right for all possible inputs?

>Solution :

You are confusing JSON null with SQL NULL. Per here JSON type:

Table 8.23. JSON Primitive Types and Corresponding PostgreSQL Types

JSON primitive type PostgreSQL type Notes

…

null (none) SQL NULL is a different concept

So:

insert into x (c1) values ('null'), ('"blah"'::json), ('{"a":1}'::json);

 select '''' || c1::text || '''' from x;
 ?column?  
-----------
 'null'
 '"blah"'
 '{"a":1}'