Postgres remove backslash in jsonb field

Question

I have a jsonb field (let's call it "info") has value like this

{"a": "foo", "c": "{ \"d\": \"dog\"}", \"b\": \"bark\"}

Using Postgres how do I remove the backslashes? I tried using

REPLACE(info::text, '\', '')

or this REGEXP_REPLACE(info::text, '\', '')

Either of them works.

Could someone please help..

\" used because this is the quoting in the string - "c": "{ \"d\": \"dog\"}". May be you need just object "c": { "d": "dog"} ? — Roman Tkachuk
– Roman Tkachuk, Commented Feb 1, 2017 at 23:54
This was a result of a json manipulation. Yes. particularly, here is the code: create or replace function update_info(info jsonb) returns jsonb language sql as $$ select json_object( array_agg(key), array_agg( case key when 'b' then lower(value) else value end))::jsonb from jsonb_each_text(info) $$; — vichsu
– vichsu, Commented Feb 2, 2017 at 0:41
I originally tried to just lower case a property inside this jsonb field like this ---{"a":"foo", "b":"BARK", "c" :{ "d": "dog"}}. So, thanks to other brilliant people who helped me with this function and I got close to it. but after running this script, I ended up with backslashes in the jsonb. — vichsu
– vichsu, Commented Feb 2, 2017 at 0:44

Roman Tkachuk · Accepted Answer · 2017-02-02 11:45:09Z

Query to lowerise jsonb:

WITH RECURSIVE z3 AS (
     WITH z2 AS (
          WITH RECURSIVE z AS (
               WITH x AS (
                   -- just your variable
                   SELECT '{"a":"foo", "b":"BARK", "c" :{ "d": "dog"}}'::jsonb AS j
               ) SELECT j,
                        ARRAY [ a ] AS a
                 FROM (
                        SELECT j,
                               jsonb_object_keys(j) AS a
                          FROM x
                      ) AS a
                 UNION ALL SELECT j,
                        a || b
                  FROM (
                        SELECT j,
                               a,
                               jsonb_object_keys(j#>(a)) AS b
                          FROM z
                         WHERE j#>>(a) ~ '^\{.*\}$'
                       ) AS A
           ) SELECT *,
                    dense_rank() OVER (ORDER BY a) AS i
               FROM z
              WHERE NOT j#>>(a) ~ '^\{.*\}$'
       ) SELECT i,
                jsonb_set(j, a, to_jsonb(lower(j#>>(a)))) AS j
           FROM (
                  SELECT * FROM z2 ORDER BY i LIMIT 1
                ) AS a
        UNION ALL SELECT z2.i,
                jsonb_set(z3.j,
                z2.a,
                to_jsonb(lower(z3.j#>>(z2.a))))
           FROM z3
           JOIN z2 ON (z2.i = z3.i + 1)
  ) SELECT j FROM z3 ORDER BY i DESC LIMIT 1;

Some explanation: z CTE looks for all string in jsonb object. 'a' is path to json string. Output:

                      j                       |   a   | dense_rank 
----------------------------------------------+-------+------------
 {"a": "foo", "b": "BARK", "c": {"d": "dog"}} | {a}   |          1
 {"a": "foo", "b": "BARK", "c": {"d": "dog"}} | {b}   |          2
 {"a": "foo", "b": "BARK", "c": {"d": "dog"}} | {c,d} |          3
(3 rows)

z3 and dense_rank is used to one per select (recursion call) apply lower to json.

Collectives™ on Stack Overflow

Postgres remove backslash in jsonb field

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