Parsing JSON Array in Postgres – PL/pgSQL – FOREACH expression must yield an array, not type text

I was trying to write a simple function to parse a JSON object in Postgres function. Basic JSON parsing was easy, but when trying to loop through the array of items,  I got stuck on this error ‘PL/pgSQL – FOREACH expression must yield an array,  not type text’ 

For example in this function, you can see that Its easy to access JSON object properties using the operator ->>.  The Name property was printing fine, but the FOREACH loop was not able to iterate through array of Items.  I knew i was missing something very basic here, but i had to spend an hour or so reading the Postgres documentation to understand this error.

As pointed in Postgres documentation (link at the end of post) the operator ->> returns a text value. Hence json_object->>’Items’ in this example evaluates to a text value,  and does not evaluate as a JSON array.  The FOREACH loop is expecting the expression on right of ARRAY to evaluate to an ARRAY object, not a text value.  That explains the error.

CREATE OR REPLACE FUNCTION parse_json () 
RETURNS VOID
AS $$
  DECLARE json_object json;
  DECLARE item json;
  BEGIN
    SELECT ('{ "Name":"My Name", "Items" :[{ "Id" : 1, "Name" : "Name 1"}, { "Id" : 2, "Name 2" : "Item2 Name"}]}')::json into json_object;
    RAISE NOTICE 'Parsing %', json_object->>'Name';
-- This FOREACH was raising the error. The expression on RIGHT of ARRAY should evaluate to an ARRAY
    FOREACH item in ARRAY (json_object->>'Items')
    LOOP
       RAISE NOTICE 'Parsing Item % %', item->>'Id', item->>'Name';
    END LOOP;
  END;
  $$ LANGUAGE 'plpgsql';
select parse_json();

Typecasting the  text to JSON array was required.  I used the function json_array_elements to convert the JSON array into a SET of JSON elements, and used a FOR loop instead.

CREATE OR REPLACE FUNCTION parse_json () 
RETURNS VOID
AS $$
  DECLARE json_object json;
  DECLARE item json;
  BEGIN
    SELECT ('{ "Name":"My Name", "Items" :[{ "Id" : 1, "Name" : "Name 1"}, { "Id" : 2, "Name 2" : "Item2 Name"}]}')::json into json_object;
    RAISE NOTICE 'Parsing %', json_object->>'Name';
    FOR item IN SELECT * FROM json_array_elements((json_object->>'Items')::json)
    LOOP
       RAISE NOTICE 'Parsing Item % %', item->>'Id', item->>'Name';
    END LOOP;
  END;
  $$ LANGUAGE 'plpgsql';
select parse_json();

Postgres Documentation for Handling JSON types:

http://www.postgresql.org/docs/9.3/static/functions-json.html

Advertisements

4 thoughts on “Parsing JSON Array in Postgres – PL/pgSQL – FOREACH expression must yield an array, not type text

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s