No Dice with JSON

[nofue]

OK, I have to admit, I just hate JSON from the bottom of my heart. However, my love doesn't grow for JSON with this seemingly simple issue:

there's a postgres table holding options, 'kinds_of_efforts' (kind_id smallint and term char varying)

After four hours of all sort of online examples and suggestions and what not, it still doesn't work for me, no matter what variant I tried.
Here's the form field which I try to poulate from

select	'f_kind'	as name
	,	'Art'		as label
	,	true		as searchable
	,	4			as width
	,	'select'	as type
	, 	'Wählen…'	as empty_option
	
	, (	select 
    		json_group_array(json_object('label', term, 'value', kind_id ))
           FROM kinds_of_efforts
      ) as options
    ;	

as taken from
#841
after all the documentation failed miserably (with the same SQL error as below).

All I can get from whatever I try is

[2025-10-03T21:08:38.407Z ERROR sqlpage::render] SQL error: In "prospects.sql": The following error occurred while executing an SQL statement:
error returned from database: function json_object(unknown, character varying, unknown, smallint) does not exist

Even casting
json_group_array(json_object('label', term::text, 'value', kind_id::integer ))
does anything but changing the the types in the error message …
Any hints as how to put those seven lines into a dropdown?

[nofue]

Well, error found: used SQLLite variant instead of PostgreSQL stuff … All fine -- who ever wants to know how this works with PostgreSQL:

     …
   , (	select json_agg(
                  json_build_object(
                      'label', term,
                      'value', result_id
                  )
               )
         from results_of_efforts
     ) as options
     …

[lovasoa]

Hi ! indeed :) Copy-pasting your message into chatgpt gives the solution ;)