How to do PostgreSQL function like FOR JSON, OPENJSON in MS SQL Server?

Question

I migrate DB from MS SQL Server to PostgreSQL.

SELECT
     DTL_CODE
    ,DTL_NAME_ENG
FROM DATA_LIST
FOR JSON AUTO

Result:

[{"DTL_CODE":"F","DTL_NAME_ENG":"Female"},{"DTL_CODE":"M","DTL_NAME_ENG":"Male"}]

And

SELECT
    *
FROM OPENJSON('[{"DTL_CODE":"F","DTL_NAME_ENG":"Female"},{"DTL_CODE":"M","DTL_NAME_ENG":"Male"}]')
WITH (
     DTL_CODE varchar(25)
    ,DTL_NAME_ENG varchar(255)
)

Result:

How to do in PostgreSQL?

I am just start PostgreSQL, I try to find solution but result 404.

Answer 1

Use json_array_elements

select j->>'DTL_CODE' as dtl_code,
       j->>'DTL_NAME_ENG' as dtl_name_eng
from json_array_elements(
    '[{"DTL_CODE":"F","DTL_NAME_ENG":"Female"},
      {"DTL_CODE":"M","DTL_NAME_ENG":"Male"}]') as j;

To do the reverse, use json_agg and json_build_object

select json_agg(json_build_object('DTL_CODE', 
                dtl_code,'DTL_NAME_ENG' , dtl_name_eng))
from t

DEMO