Getting JSON field key names in Postgres

user_129805 0
Viewed 6229

I have data which uses JSON as tagged unions, such that a top-level object contains just one child object. The type of the child object depends on its key-name in the parent, not a separate "tag" field, as would be normal in C structs.

e.g.

{"circle":{"radius":10}}
{"square":{"side":10}})
{"rectangle":{"width":10,"height":20}})

This works very nicely with JSON Schemas and Protocol Buffers.

I have read: https://www.postgresql.org/docs/9.6/static/functions-json.html

I am struggling with Postgres's JSON functions. How do I do the SQL equivalent of the following Javascript

Object.keys({"circle":{"radius":10}})[0]               (== `"circle")
Object.keys({"square":{"side":10}})[0]                 (== `"square")
Object.keys({"rectangle":{"width":10,"height":20}})[0] (== `"rectangle")

with JSONB fields?

postgresql-9.6 jsonb postgresql json
2 Answers

You can use a jsonb_object_keys to get the column keys in jsonb

SELECT jsonb_object_keys(json_column)
FROM foo;

For example:

SELECT distinct jsonb_object_keys(metadata)
FROM department.metadata;
user_171325780
Related