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?

This looks relevant: stackoverflow.com/a/38347906/129805

fadedbee
– fadedbee

2017-07-24 12:37:18 +00:00
Commented Jul 24, 2017 at 12:37 — fadedbee
– fadedbee, Commented Jul 24, 2017 at 12:37

Martin Campbell · Accepted Answer · 2017-07-24 12:53:49Z

10

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.

edited Jul 24, 2017 at 12:53

answered Jul 24, 2017 at 12:40

Martin Campbell

1,79812 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

fadedbee Over a year ago

Is there a way to get the first object key, i.e. turning a record set into a scalar?

Martin Campbell Over a year ago

I've edited the answer to put an example of getting a scalar value out

Andronicus · Accepted Answer · 2022-04-07 06:42:35Z

0

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;

edited Apr 7, 2022 at 6:42

Andronicus

26.2k18 gold badges55 silver badges92 bronze badges

answered Apr 7, 2022 at 6:14

Tea Kesh

2012 silver badges2 bronze badges

Collectives™ on Stack Overflow

Getting JSON field key names in Postgres

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related