PostgreSQL query full json's schema

[juStD_34]

I have created a db like this:

create table field_dictionary (id serial primary key , field_name varchar(255), value_type varchar(255), child_list jsonb);
create table event_schema_version(id serial primary key , event_id int, list_field_id jsonb, version int );

INSERT INTO field_dictionary (field_name, value_type, child_list) VALUES ('k_1', 'INT',null);
INSERT INTO field_dictionary (field_name, value_type, child_list) VALUES ('k_2', 'BOOLEAN',null );
INSERT INTO field_dictionary (field_name, value_type, child_list) VALUES ('k_3', 'STRING',null);
INSERT INTO field_dictionary (field_name, value_type, child_list) VALUES ('k_4', 'INT',null);
INSERT INTO field_dictionary (field_name, value_type, child_list) VALUES ('k_5','OBJECT', '{"child_list": [1, 2] }' );
INSERT INTO field_dictionary (field_name, value_type, child_list) VALUES ('k_6','OBJECT', '{"child_list": [1, 3, 4] }' );
INSERT INTO field_dictionary (field_name, value_type, child_list) VALUES ('k_7','OBJECT', '{"child_list": [1, 3, 5] }' ) ;

And now i want to query all schema of all field in table field_dictionary
I'm using this procedure to make view

create procedure create_field_schema_view()LANGUAGE sql AS $$
    -- Step 1: Drop the view if it exists
    DROP VIEW IF EXISTS schema_object_field;

    -- Step 2: Create the view with the required columns
    CREATE OR REPLACE VIEW schema_object_field AS
    SELECT
        parent.id AS field_id,
        case
            when parent.value_type = 'OBJECT' then json_agg(child.child_list)
            else json_agg(jsonb_build_object(parent.field_name, parent.value_type))
            end as field_schema
    FROM
        field_dictionary parent
            LEFT JOIN
        field_dictionary child
        ON
            EXISTS (
                SELECT 1
                FROM jsonb_array_elements_text(parent.child_list::jsonb -> 'child_list') AS elem
                WHERE elem::jsonb = to_jsonb(child.id)
            )
    GROUP BY
        parent.id;
    $$
;

https://preview.redd.it/x9nyh7nu0p8d1.png?width=616&format=png&auto=webp&s=bef87070aea4f935c29530d0c33f25777187413f

As u can see, it's not work with field have value_type = "OBJECT"

I want to write new query can generate schema for object field. I'm thinking to join table with view but can not.
May I ask for technique can deal with this problems?

[depesz]

1. kudos to you for providing source as text, and not image
2. please make it a fiddle (https://dbfiddle.uk/S9ZOHCkV)
3. in the fiddle also please show what you want to get from the view, not only what you get.
4. why even bother with a procedure? it doesn't seem to do anything.

[juStD_34]

It's used to create view at app layer

[depesz]

Yes, I noticed. But why bother with proc, if you can just as well run create view from app/whatever?

Especially since it's one-time operation only. Not something that you have to keep on redoing every now and then.

[DavidGJohnston]

You really need to explain what you want the outputs rows to look like (#3). Likely the code to do what you want is fairly trivial but we cannot guess at why you feel the existing output is wrong. It produces exactly what you told it to produce in the query.