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"
      }

Simeon Pilgrim · Accepted Answer · 2021-10-18 05:01:06Z

1

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

edited Oct 18, 2021 at 5:01

answered Oct 18, 2021 at 4:52

Simeon Pilgrim

26.7k3 gold badges38 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3461502 Over a year ago

Thank you! What if I have several objects within "Photo"? Will this method work?

Simeon Pilgrim Over a year ago

@user3461502 the criteria you stated was "include it if it has a photo", thus if "it has many photo's" is still worthy of including (aka instead of an object {} photo is an array []) then this still will be good when not null? unless I have missed something?

FKayani · Accepted Answer · 2021-10-18 08:04:03Z

1

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

answered Oct 18, 2021 at 8:04

FKayani

9061 gold badge7 silver badges10 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related