Sort by partial key name in json column

Question

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->'blocks'->'participant-information_primary*'->>'first_name'

How can I do it?

• András Váczi: I'd store the integer part separately. Then you may be able to have a slightly different JSON structure that can be searched to your liking.
• kritik: Yes, but I need to save here id. But I found an answer on my question. Will post it.

Answer 1

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->'blocks') AS part
ORDER BY document->'blocks'->part->>'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->'blocks') part
 WHERE part ~ '^participant-information_primary__Primary__' -- your pattern here
 LIMIT 1 -- just to be safe
 ) s ON true
ORDER BY document->'blocks'->part->>'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?

• kritik: Great, we have one more solution

Answer 2

Solution to the problem

 -- field should be escaped already
 CREATE OR REPLACE FUNCTION json_sort_by(_j json, field text) RETURNS text AS $$
 declare key text;
 declare search text[];
 BEGIN
 search := string_to_array(field, '.');
 IF field ~ '^blocks' THEN
 FOREACH key IN ARRAY ARRAY(select json_object_keys(_j->'blocks')) LOOP
 IF key ~ ('^'::text || search[2]) THEN
 return _j->'blocks'->key->>search[3];
 END IF;
 END LOOP;
 return '';
 ELSE
 return json_extract_path_text(_j, VARIADIC search);
 END IF;
 END;
 $$
 LANGUAGE plpgsql IMMUTABLE;

And then in SQL query run order by json_sort_by(json, 'fddf.fdfd.fdfd')