How to query the value of the first available key of a jsonb object with postgresql?

Question

So I have this table:

create table
  public.listings (
    id bigint generated by default as identity,
    design_titles jsonb not null default '{}'::jsonb,
    ...removed for readablilty
  ) tablespace pg_default;

my goal is to get the value of the first available key in the object at the design_titles column.

The supabase AI generated this code but I don't like that this uses two queries:

SELECT
  (
    design_titles ->> (
      SELECT
        jsonb_object_keys(design_titles)
      FROM
        public.listings
      WHERE
        id = '11'
    )
  ) AS first_design_title
FROM
  public.listings
WHERE
  id = '11';

Is there a better and more efficient way to do this?

Answer 1

Maybe little bit faster form (tested on PostgreSQL 16):

postgres=# select (jsonb_path_query_array('{"a":10, "b":20}'::jsonb,'$.keyvalue()'))[0]->'value';
┌──────────┐
│ ?column? │
╞══════════╡
│ 10       │
└──────────┘
(1 row)

or probably faster

postgres=# select jsonb_path_query_first('{"a":10, "b":20}'::jsonb,'$.keyvalue()')->'value';
┌──────────┐
│ ?column? │
╞══════════╡
│ 10       │
└──────────┘
(1 row)