0

Trying to concat two json text fields like so:

SELECT app->>'id' || app->>'version' as foo
FROM table,
    json_array_elements(applications::json) as app;

Where applications is the data type text, results in the error:

LINE 3: SELECT app->>'id' || app->>'version' as foo
                                ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

I've also tried with jsonb. What is happening here, and how should json text fields be concatenated together?

Improve this question
3
  • You just need to cast the values to the appropriate data types. Try something like this SELECT (app->>'id')::text || app->>'version' as foo
    – GoonerForLife
    Jan 26 at 18:25
  • If the columnapplications really contains JSON values, it should be declared as a jsonb column so that you don't need to cast it every time you want to do something useful with it.
    – user1822
    Jan 26 at 18:47
  • 1
    You have a precedence problem. Your expression is interpreted as (app->>'id' || app)->>'version'. Add explicit parentheses to clarify the order of the operators.
    – jjanes
    Jan 26 at 18:49

1 Answer 1

Reset to default
2

I think || binds stronger than the ->> operator, so your expression is evaluated as app ->> ('id'||app) ->> 'version'

Enclose each expression in parentheses to avoid this:

(app->>'id') || (app->>'version')
Improve this answer

Your Answer

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

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