Question 234753 in Mongodb Question posted in
The official documentation can be found here.

Mongodb – How to query a nested object array

The picture shows example oF JSON data

I am trying to query "id" value under images field in aws redshift mode. I tried

SELECT json_extract_path_text(images, 'id')
FROM table_name;

This query give me an error

ERROR: function json_extract_path_text(super, "unknown") does not
exist Hint: No function matches the given name and argument types. You
may need to add explicit type casts.

Thanks for the help.

Tags:

2

Answers


  1. 0

    Please Try This:

    SELECT image_obj->>'id' AS image_id
FROM table_name,
     json_array_elements(images) AS image_obj;
    Login or Signup to reply.
  2. 0

    Try This Way:

    Assuming your table is named table_name and the JSON array field is named images, and each element in the images array has an id attribute you want to extract:

    SELECT image_obj.image_id
    FROM table_name
    CROSS JOIN (
    SELECT JSON_PARSE(images) AS images_array
    FROM table_name
    ) AS parsed
    CROSS APPLY (
    SELECT value->>’id’ AS image_id
    FROM parsed.images_array
    CROSS JOIN LATERAL json_array_elements(images_array) AS value
    ) AS image_obj;

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search