How to stringify PostgreSQL JSON data?

Question

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?

Adrian Klaver · Accepted Answer · 2022-04-28 18:34:12Z

1

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}'

answered Apr 28, 2022 at 18:34

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user9645 Over a year ago

Ah - OK great, thanks. Do you know if this works for all cases or should I do an exhaustive test?

Collectives™ on Stack Overflow

How to stringify PostgreSQL JSON data?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related