Sort by partial key name in json column I have column document of data type json with logging data like:

CREATE TABLE foo(document)
AS
  VALUES (
    '{
      "blocks": {
        "participant-information_primary__Primary__39011__2288960": {
            "first_name": "First name"
        }
      }
     }'::jsonb
  ), (
    '{
      "blocks": {
        "participant-information_primary__Primary__39011__2288969": {
            "first_name": "Joe ZZZ"
        }
      }
     }'
  ), (
    '{
      "blocks": {
        "participant-information_primary__Primary__39011__2288169": {
            "first_name": "AAA ZZZ"
        }
      }
     }'
  ), (
    '{
      "blocks": {
        "participant-information_primary__Primary__39011__1288169": {
            "first_name": "kkk"
        }
      }
     }'
  );


Key name 'participant-information_primary__Primary__39011__2288960' is dynamic and can vary by the trailing integer number. I want to do something like:

ORDER BY document-&gt;'blocks'-&gt;'participant-information_primary*'-&gt;&gt;'first_name'


How can I do it?
Using json_object_keys() like you found yourself already (or jsonb_object_keys() for jsonb), you can do this with simple inline SQL. I suggest a LATERAL subquery.

If, like your example suggests, there is exactly one key inside the key  'blocks', the solutions is simple:

SELECT *
FROM   tbl, json_object_keys(document-&gt;'blocks') AS part
ORDER  BY document-&gt;'blocks'-&gt;part-&gt;&gt;'first_name';


The comma is short notation for CROSS JOIN LATERAL.

If there can be any number of keys, you need to be more verbose:

SELECT *
FROM   tbl
LEFT   JOIN LATERAL (
   SELECT part
   FROM   json_object_keys(document-&gt;'blocks') part
   WHERE  part ~ '^participant-information_primary__Primary__'  -- your pattern here
   LIMIT  1  --  just to be safe
   ) s ON true
ORDER  BY document-&gt;'blocks'-&gt;part-&gt;&gt;'first_name';


The LEFT JOIN preserves rows where no matching key is found at all.

Related:


What is the difference between LATERAL and a subquery in PostgreSQL?