0

A column in the postgres DB (14.2) was historically a text before I've started writing serialized json objects into it so current type is text.

Currently most of the rows looking like

"{\"RecomendationToFriend\":3,\"Improve\":\"test\"}"

What I need is to select RecomendationToFriend so I'm converting the column to jsonb using to_jsonb():

select to_jsonb(f."Text")->'RecomendationToFriend', to_jsonb(f."Text")  from "Feedback"

to_jsonb() seems to work and shows the objects in dbeaver (second column) but trying to retrieve RecomendationToFriend out of it return nulls (first column)

enter image description here

How can I retrieve RecomendationToFriend as numbers so I can aggregate them later?

4
  • 1
  • it's neither code nor error but just the example of data retrieved by my select statement to show what I mean. Everything else is a text
    – VladL
    Apr 14, 2023 at 18:45
  • Nevertheless, readers are not going to expand the image to read it, in order yo help you. You need to make it easy for them. Apr 14, 2023 at 20:19
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Apr 14, 2023 at 20:19

1 Answer 1

2

There are layers to this mess. At the core, you double-quoted strings that should have been single-quoted, thereby escaping all double-quotes.

To undo, I suggest this simple helper function:

CREATE OR REPLACE FUNCTION f_unescape(text, OUT _t text)
  LANGUAGE plpgsql STABLE PARALLEL SAFE AS
$func$
BEGIN
   EXECUTE 'SELECT E''' || $1 || ''''
   INTO _t;
END
$func$;

Detailed explanation:

You may need to do more. That's also addressed over there.

Then trim leading and trailing double-quote. Assuming there is no leading / trailing white space. Else you need to trim that, too.

An then cast to ::json or ::jsonb instead of to_json().

SELECT f_unescape(trim("Text", '"'))::json -> 'RecomendationToFriend' AS result
FROM   "Feedback" f;

fiddle

That requires legal JSON as input, though ...

Of course, you should not use "Text" as column name to begin with. That's bad for at least 4 reasons. Nor "Feedback" as table name. Use legal, lower-case, unquoted, descriptive identifiers to avoid more confusion. See:

1
  • Thanks, I think I'll just write a migration on the backend then. Regarding naming - they are coming from the default entity frameworkk core configuration. Escaping comes also as the result of .NET serialization
    – VladL
    Apr 15, 2023 at 8:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.