CODEWITHSUNDEEP
jsonpostgresqlpostgresql-json
Alexandr Lazarev
Alexandr Lazarev

Reputation: 12892

PostgreSQL. json_object_agg() returns text string instead of json object

As it is written here, json_object_agg(name, value) return type is json. Meanwhile, if I return the value returned by json_object_agg() from a stored procedure:

CREATE OR REPLACE FUNCTION _getlocales()

RETURNS json AS
$BODY$DECLARE
    var json;
BEGIN
    select into var json_object_agg("key", "values") from table;
    RETURN var;
END;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

And call it in another function:

variable    =   _getlocales();    
RAISE NOTICE 'DATA TYPE %', pg_typeof(variable);
SELECT variable->>'property'

I can not get JSON object field. I run into:

ERROR: operator does not exist: text ->> unknown

And raised notice shows that data type is text.

NOTICE: DATA TYPE text

I've tried to change _getlocales() return type to jsonb, but nothing was changed:

CREATE OR REPLACE FUNCTION nav._getlocales()
RETURNS jsonb AS
$BODY$DECLARE
    _l18nJson   jsonb;
...

Why this happens?

Upvotes: 4

Views: 3260

Answers (1)

Alexandr Lazarev
Alexandr Lazarev

Reputation: 12892

@Richard Huxton has pointed me the right direction, I was inattentive and I've declared variable with text data type. So, I've changed:

$BODY$DECLARE
    variable text;

To: 

$BODY$DECLARE
    variable json;

And the issue was solved.

Upvotes: 1

Related Questions