Querying Nested JSON with Path | Dataford Interview Questions

Given a Json Object in the form of Map<String, Object>, and given a query to match a key by path (similar to jq), extract its value. Asked in the phone screen stage. For example, the path .contacts.cell returns the nested cell value, while a missing path returns null. Reported follow-up: extend the functionality to support any key (*) match.

Write a PostgreSQL query using the stored JSONB object and requested path.

Output

One row per path request with request_id, path_query, and extracted value.
Return null when the object or path does not exist.
Order by request_id ascending.

Schema

json_objects

Column	Type	Description
object_idPK	INT	Identifier of the stored JSON object
payload	JSONB	JSON document to query

path_requests

Column	Type	Description
request_idPK	INT	Identifier of the path lookup request
object_id	INT	JSON object requested for lookup
path_query	VARCHAR(255)	jq-style path beginning with a dot

Your question is Querying Nested JSON with Path. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem