7

I want to extract a JSON property from a JSON document. Let's simplify my example as following:

create table a(a text);
insert into a(a) select '{"name":"Kevin"}'::json -> 'name';

Now the table a has a value "Kevin" instead of Kevin. Do I really have to manually extract the quotes?

I am using PostgreSQL v10.1.

1 Answers1

33

The JSON operator -> returns a JSON value. That's why it's quoted. If you want a plain text, use the operator that returns a text value: ->>

select '{"name":"Kevin"}'::json ->> 'name';
  • 1
    That makes sense. So if some proprety `enabled` is a boolean value, I need to do `(json ->> 'enabled')::boolean`? There is no operator to automatically convert to the corresponding type? Always first to text? –  Jan 03 '18 at 20:50
  • JSON has no "boolean" type - in fact JSON has no data types at all. In JSON everything is a string. –  Jan 03 '18 at 20:53
  • 1
    I am confused by that. See https://www.w3schools.com/js/js_json_datatypes.asp and PostgreSQL function `json_typeof`. –  Jan 03 '18 at 20:55
  • w3fools is known to be full of errors. But anyway, the JSON values in Postgres are either "JSON" or text. If you want properly typed values, don't use JSON. –  Jan 03 '18 at 20:56
  • 2
    That's odd... what about `json_typeof()`, which returns `object, array, string, number, boolean, null`? See also http://www.json.org/value.gif on http://www.json.org/ and https://stackoverflow.com/a/383699/4597030. –  Jan 03 '18 at 20:58