postgresql text to json conversion

Question

I have in the database a table query_config, which will contain scripts for querying, as well as other configuration values. This is the reason why I use json type. Table as follows:

CREATE TABLE query_config 
(
   id integer, 
   execute_query json 
);

In this table I want to insert eg:

INSERT INTO query_config(id, execute_query)
    VALUES (4, ('{"query": ' || 
    '"select *
    from tests
    where tests.id > 5
    order by moment desc"}')::json);

But I keep getting the error:

ERROR: invalid input syntax for type json 
DETAIL: Character with value 0x0a must be escaped.

What am I doing wrong please and how do I escape newline character?

Clodoaldo Neto · Accepted Answer · 2014-09-17 17:22:06Z

Use to_json to make it valid json and dollar quoting so it will swallow any text in the query

select format('{%s: %s}',
    to_json('query'::text), 
    to_json($query$
        select *
        from tests
        where tests.id > 5
        order by moment desc
    $query$::text)
)::json;
                                                       format                                                        
---------------------------------------------------------------------------------------------------------------------
 {"query": "\n        select *\n        from tests\n        where tests.id > 5\n        order by moment desc\n    "}

http://www.postgresql.org/docs/current/static/functions-json.html#FUNCTIONS-JSON-TABLE

http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS

The format function is just to make it easy on the eyes

http://www.postgresql.org/docs/current/static/functions-string.html#FUNCTIONS-STRING-OTHER

cur4so · Accepted Answer · 2014-09-17 05:02:09Z

4

it will work if you remove new lines, in other words, put the json value on a single line like:

INSERT INTO query_config(id, execute_query)
    VALUES (4, ('{"query":' || '"select * from tests where tests.id > 5 order by moment desc"}')::json);

answered Sep 17, 2014 at 5:02

cur4so

1,8204 gold badges21 silver badges29 bronze badges

1 Comment

peterko Over a year ago

yes, it is an option, but a code that will store there may need to be changed, therefore I want to kept newlines.

Collectives™ on Stack Overflow

postgresql text to json conversion

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related