How to search for a particular object value inside a nested JSON array?

Question

I am trying to select all the sections that have a "photo" within "employee" which is found within "employees". If they don't have a "photo", I do not want to select this section. Is there a way to do this? I am using SQL in SNOWFLAKE.

For example, I have this JSON:

 {
      "employees": {
        "employee": [
          {
            "id": "1",
            "firstName": "Tom",
            "lastName": "Cruise",
            "photo": "https://pbs.twimg.com/profile_images/735509975649378305/B81JwLT7.jpg"
          },
          {
            "id": "2",
            "firstName": "Maria",
            "lastName": "Sharapova"
          },
          {
            "id": "3",
            "firstName": "James",
            "lastName": "Bond"
          }
        ]
      }
    }

Since the last two don't have "PHOTO" I do not want this return. So for the above, it should only return:

      {
        "id": "1",
        "firstName": "Tom",
        "lastName": "Cruise",
        "photo": "https://pbs.twimg.com/profile_images/735509975649378305/B81JwLT7.jpg"
      }

user_43992 · Answer

SELECT e AS json
FROM data_table t,
LATERAL FLATTEN(input=>t.json:employees:employee) e,
WHERE e.value:photo IS NOT NULL;

user_12396770 · Answer

YOu may consider option 4. For a more dynamic way to achieve similar results, where the position of the elements is not deterministic, Lateral Flatten with unpivot function can be handy, in the below article: https://community.snowflake.com/s/article/Dynamically-extracting-JSON-using-LATERAL-FLATTEN

How to search for a particular object value inside a nested JSON array?

2 Answers