Escaping characters in PostgreSQL

Question

I have a very large database populated from social media. I'm trying to make a new column to make JSON for word_counter for faster analytics.

I'm first creating a function in PostgreSQL to take a string array, count the occurrences, and return a jsonb that gets inserted. Here is the following function

 CREATE
OR REPLACE FUNCTION count_elements (TEXT []) RETURNS JSONB AS $$
DECLARE js JSONB := '{}' ;
DECLARE jjson JSONB ;
BEGIN
    SELECT
        jsonb_agg (
        (
                '{"' || i|| '":"' || C || '"}'
            ) :: JSONB
        ) INTO jjson
    FROM
        (
            SELECT
                i,
                COUNT (*) C
            FROM
                (SELECT UNNEST($1 :: TEXT []) i) i
            GROUP BY
                i
            ORDER BY
                C DESC
        ) foo ; RETURN jjson ;
    END ; $$ LANGUAGE plpgsql;

Here is the issue. When running the following query

select count_elements(string_to_array(lower(tweet_text), ' ')),tweet_text from tweet_database

limit 10

I get this error

[Err] ERROR:  invalid input syntax for type json
DETAIL:  Character with value 0x0a must be escaped.
CONTEXT:  JSON data, line 1: {"winning?
SQL statement "SELECT

I tried escaping the column, and then regex replacing some of the items but it hasn't worked yet.

to_json will perform escaping for you.

teppic
– teppic

2017-09-26 19:41:09 +00:00
Commented Sep 26, 2017 at 19:41 — teppic
– teppic, Commented Sep 26, 2017 at 19:41

teppic · Accepted Answer · 2017-09-26 20:49:31Z

3

the to_json function can be used to escape text:

SELECT
  jsonb_agg (
      (
        '{' || to_json(i) || ':' || C || '}'
      ) :: JSONB
  ) INTO jjson

then

select count_elements(E'{a, a, b, a\nb, a}'::text[]);

results in

[{"a":3}, {"b":1}, {"a\nb":1}]

edited Sep 26, 2017 at 20:49

answered Sep 26, 2017 at 20:08

teppic

7,3161 gold badge34 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Escaping characters in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related