Sort by partial key name in json column
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?