Getting JSON field key names in Postgres

Question

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?

Martin Campbell Martin Campbell · Accepted Answer · 2017-07-24T12:40:47

There is a jsonb_object_keys that you can use which is like its Javascript counterpart. For example:

SELECT jsonb_object_keys(json_column)
FROM MyTable

This obviously returns a record set. However, if you know that there will only ever be one key in a JSON object then just use this as a sub query if this is required. For example:

SELECT *
FROM MyTable
WHERE 'cicle' = (
  SELECT jsonb_object_keys(json_column)
  FROM MyTable
)

EDIT

You could get a scalar value as follows:

SELECT json_build_array(jsonb_object_keys(json_column)) -> 0
FROM MyTable

Note this is json (i.e. "circle", not circle). If you need the text value, use the ->> operator.

Getting JSON field key names in Postgres

1 Answers