CODEWITHSUNDEEP
postgresqljsonb
user2723240
user2723240

Reputation: 823

Escaping characters in PostgreSQL

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.

Upvotes: 1

Views: 6685

Answers (1)

teppic
teppic

Reputation: 7286

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}]

Upvotes: 3

Related Questions