Concat two jsonb/json fields yields error

Question

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?

• GoonerForLife: You just need to cast the values to the appropriate data types. Try something like this SELECT (app->>'id')::text || app->>'version' as foo
• user1822: 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.
• jjanes: You have a precedence problem. Your expression is interpreted as (app->>'id' || app)->>'version'. Add explicit parentheses to clarify the order of the operators.

Answer 1

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')