3

There does not seem to be an obvious way:

  • select 'a123'::text::jsonb = ERROR: invalid input syntax for type json
  • select '"a123"'::text::jsonb = BAD string because quoted
    check select '"a123"'::text::jsonb = ('{"x":"a123"}'::jsonb)->'x'
    to see that non-quoted is the correct.
  • select '123'::text::jsonb = ('{"x":123}'::jsonb)->'x'; = NOT string

I need '123' and 'a123' as pure JSONb strings.

PS: it is not a duplicate of generic automatic-anything conversion.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Peter Krauss
  • 13,174
  • 24
  • 167
  • 304
  • What do you want to achieve? The quoted version is THE valid jsonb string. IMHO there's no other un-quotated representation. – S-Man Nov 23 '20 at 19:33

1 Answers1

5

To convert untyped string literals, that are not wrapped in double-quotes to jsonb (or json), use the to_jsonb() (or to_json()) function:

SELECT to_jsonb(text 'a123');

Note that the input has to be a string type (text, varchar, ...), not an untyped literal. That's how Postgres knows you want a JSON string.

The above text 'a123' is one way to cast an untyped literal. There are others:

For a direct cast to json(b), Postgres expects valid JSON literals (with double-quoted strings):

SELECT '"a123"'::jsonb;

To translate each value to a specific JSON primitive, you can cast conditionally before the conversion. Example:

SELECT p, CASE WHEN i>2 THEN to_jsonb(p::numeric) ELSE to_jsonb(p) END AS x
FROM   unnest('{ab,12,12,1.2}'::text[]) WITH ORDINALITY t(p,i);

select '"a123"'::text::jsonb = BAD string because quoted

To be precise, the result is not a string, but a jsonb value containing a JSON string. To get the string as Postgres data type text, you need the ->> operator:

select 'a123'::text  = ('{"x":"a123"}'::jsonb)->>'x'

Or (comparing JSON values):

select '"a123"'::jsonb = ('{"x":"a123"}'::jsonb)->'x';

I need '123' and 'a123' as pure JSONb strings.

So:

SELECT '"123"'::jsonb, '"a123"'::jsonb;

Both contain JSON strings.

This also works:

SELECT '123'::jsonb;

.. but contains a JSON numeric.

But this does not work:

SELECT 'a123'::jsonb;  -- error

.. because it's not a valid numeric literal.

The manual has a table of mappings between JSON Primitive Types and Corresponding PostgreSQL Types

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Hum... But the question is about "jsonB-string", not "how to cast to SQL-text" – Peter Krauss Nov 23 '20 at 20:13
  • I need to input SQL-strings and preserve it into the database as **correct** JSONb types... I need to say to PostgreSQL: plese a *123* is JSONb string, plese a *a123* is JSONb string, etc. – Peter Krauss Nov 23 '20 at 20:15
  • jsonb strings (or json for that matter) are represented as double-quoted string literals. Other JSON primitives work without double quotes: `null`, `boolean` and `numeric`. – Erwin Brandstetter Nov 23 '20 at 20:15
  • Ok, but how to input it? `select '"a123"'::text::jsonb = ('{"x":"a123"}'::jsonb)->'x'` is false, I need to input into database a non-quoted string. – Peter Krauss Nov 23 '20 at 20:17
  • Ah, that's the missing bit. I added the solution at the top. – Erwin Brandstetter Nov 23 '20 at 20:22
  • Perfect! Now `select to_jsonb(text 'hello') = ('{"x":"hello"}'::jsonb)->'x'` and it works also with 123. – Peter Krauss Nov 23 '20 at 21:02
  • OOPS! On Postgresql version 12.5 `select p, to_jsonb(text p) from unnest(array['12','ab']::text[]) t(p)` is a **syntax error**. Only works with literal constants, `select to_jsonb(text 'p')`. – Peter Krauss Nov 24 '20 at 13:28
  • Yes, that syntax variant only works for casting literals. See: https://stackoverflow.com/a/13676871/939860 – Erwin Brandstetter Nov 24 '20 at 15:52