JSONB existence (?) operator in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the JSONB existence (?) operator check in PostgreSQL?

It checks if a specified key exists in a JSONB column.

beginner

How do you use the JSONB existence operator to check if the key 'name' exists in a JSONB column called 'data'?

Use the query: SELECT * FROM table WHERE data ? 'name';

beginner

What type of value does the JSONB existence (?) operator return?

It returns a boolean: true if the key exists, false if it does not.

intermediate

Can the JSONB existence (?) operator check for keys inside nested JSON objects?

No, it only checks for top-level keys in the JSONB column.

intermediate

What is the difference between the JSONB existence (?) operator and the containment (@>) operator?

The existence (?) operator checks for a single key's presence, while the containment (@>) operator checks if one JSONB contains another JSONB structure.

Which operator checks if a key exists in a JSONB column in PostgreSQL?

A #>

B @>

C ->

D ?

The '?' operator checks if a key exists in a JSONB column.

What does the query SELECT * FROM users WHERE info ? 'email'; return?

A Rows where 'email' key exists in 'info' JSONB column

B Rows where 'email' key does not exist

C Rows where 'email' value equals true

D Rows where 'info' is null

The '?' operator filters rows where the specified key exists in the JSONB column.

Can the JSONB existence (?) operator check for nested keys inside JSON objects?

A Yes, it checks all nested keys

B No, only top-level keys

C Only if keys are arrays

D Only if keys are strings

The '?' operator only checks for top-level keys in the JSONB data.

What type of value does the JSONB existence (?) operator return?

A Boolean

B String

C Integer

D JSONB

It returns a boolean indicating if the key exists.

Which operator would you use to check if a JSONB column contains a specific JSON structure?

A ?

B ->

C @>

D #>

The '@>' operator checks if one JSONB contains another JSONB structure.

Explain how the JSONB existence (?) operator works and give an example query.

Think about checking if a key is present in a JSON object stored in a column.

Describe the limitations of the JSONB existence (?) operator regarding nested JSON keys.

Consider how deep the operator looks inside the JSON structure.