There's one other type of inspection that we can do on JSON blobs and that is existence. We've done extraction, encapsulation. Now we're going to do existence. This is where you can just merely check if an object contains a key or an array contains an item. There are a couple different options we have here. Here's a hard-coded JSON blob we'll move into a table here in a second but the very basic existence operator is this question mark. Then you are testing to see if it contains a key.

Here we're saying, "Does this blob contain status?" In fact, it does. Does it contain foo? It does not. It also works with arrays. You can say, "Does this array contain apple?" Yes. Does it contain asdf? No, it does not. That's the very basic existence operator. You have two other variants. One of them is does it contain any of the following.

We can say, "Does it contain? Does it contain status or does it contain, let's just make one up, created_at?" And we see that it does contain one of those two. In fact, it contains status because if we were to change this right here, it would go to false. This is testing to see does it contain any of those. You guessed it, what's coming next is does it contain all of those? Let's just change customer just for giggles to created_at. You'll see that now it contains all of those keys, whereas here it does not contain all of those keys but it does contain any of those keys. Again, we can use this to filter down rows. We can use this as a part of our where clause. In fact, let's do that right now. We still have this orders, JSON, hanging around. Let's just do where. Then I believe it's called details contains status. This one we just want to know, give us all of the orders that have a status. In this case, it is all five.

We could say, "Give us all of the orders that have a status or a pending status," if we've designed a poor schema here. This would give us exactly what we need. In fact, this might be a good way to test if this poorly-designed schema has some bad data in it 'cause if there's anything that has a status and a pending status, that doesn't make a lot of sense to me. We should probably have one or the other. We could use the and existence operator here to check to see if any of these blobs contain both of those keys. If so, they need to go into some sort of remediation queue to figure out what the heck is going on here.

That's JSON existence. Not a whole lot there. You have the basic operator, which tests one key. You have the or or the any operator, which tests does it contain any of those keys. You have the all or the and operator, which says it must contain all of these keys.