postgres string to json

Question

I have a postgres table with col - fields.

Values can be '',NULL or string like - {"key":"value"}

while converting the string to json and accessing the object, I am getting below error:

Error - Unable to cast object of type 'System.DBNull' to type 'System.String'.

I ran the below query:

select to_json(field)->>'rest_uri' from dbo.tablename

Answer 1

PostrgeSQL needs a datatype to determine the type of json value, when we select from a table the column type is known so a null value is accepted.

create table dbo_tablename (field varchar(25));
insert into dbo_tablename values ('test'),(null);

?

2 rows affected

select to_json(field) from dbo_tablename;

| to_json |
| :------ |
| "test"  |
| null    |

select jsonb_agg(dbo_tablename)
from dbo_tablename

| jsonb_agg                            |
| :----------------------------------- |
| [{"field": "test"}, {"field": null}] |

*db<>fiddle herees_14&fiddle=e9edbef66da563358b847d828986808c)