Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using PostgreSQL, how do I escape "\" in json columns?

I'm using Postgres 9.5 and I have a column ("info") of type 'json'... I'm trying to do this insert:

INSERT INTO table ( info )
VALUES (
  '{"entry":"((\+)[0-9]+)"}'
)

but I get this error:

ERROR:  invalid input syntax for type json
DETAIL:  Escape sequence "\+" is invalid.

It's interpreting \+ as an escape sequence but I actually want that as part of my value.

like image 571
user3228515 Avatar asked Apr 19 '17 17:04

user3228515


People also ask

How do I escape a character in PostgreSQL?

PostgreSQL also accepts “escape” string constants, which are an extension to the SQL standard. An escape string constant is specified by writing the letter E (upper or lower case) just before the opening single quote, e.g., E'foo' .

What is ->> in PostgreSQL?

PostgreSQL provides two native operators -> and ->> to help you query JSON data. The operator -> returns JSON object field as JSON. The operator ->> returns JSON object field as text.

What does escape JSON mean?

"}"; // escapeJson: Escapes the characters in a String using Json String rules. // Escapes any values it finds into their Json String form. Deals correctly with quotes and control-chars (tab, backslash, cr, ff, etc.) // So a tab becomes the characters '\\' and 't'.


1 Answers

In general,

  • if you have keys and values use jsonb_build_object to construct the object
  • if you're writing a literal JSON object to cast from string, then it must be proper; a proper JSON string requires escaping \.

Explanation

PostgreSQL isn't quoting this: it's just the JSON implimentation following RFC 7159.

A string begins and ends with quotation marks. All Unicode characters may be placed within the quotation marks, except for the characters that must be escaped: quotation mark, reverse solidus, and the control characters (U+0000 through U+001F). [... ] So, for example, a string containing only a single reverse solidus character may be represented more compactly as "\\".

So it looks like this in the literal form.

CREATE TABLE tbl
AS
  SELECT '{"entry":"((\\+)[0-9]+)"}'::jsonb AS info;

Dollar-quoting in PostgreSQL requires no-escapes, but it will not help here,

Notice that inside the dollar-quoted string, single quotes can be used without needing to be escaped. Indeed, no characters inside a dollar-quoted string are ever escaped: the string content is always written literally. Backslashes are not special, and neither are dollar signs, unless they are part of a sequence matching the opening tag.

So this will not work because \+ is not a valid string in JSON. It would work if we were not using a json type though.

SELECT '{"key":"\"}'::jsonb;
ERROR:  invalid input syntax for type json
LINE 1: SELECT '{"key":"\"}'::jsonb;
               ^
DETAIL:  Token ""\"}" is invalid.
CONTEXT:  JSON data, line 1: {"key":"\"}

However, you can use to_jsonb() to JSON-escape the strings..

SELECT FORMAT( $${%s:%s}$$, to_jsonb(k), to_jsonb(v) )::jsonb
FROM ( VALUES
  ('key', '\' )
) AS t(k,v);

But, even this is a bad idea because if you have the keys and values you can use json_build_object,

SELECT jsonb_build_object( k, v )
FROM ( VALUES
  ('key', '\' )
) AS t(k,v);
like image 138
NO WAR WITH RUSSIA Avatar answered Oct 13 '22 04:10

NO WAR WITH RUSSIA