Get selected list of columns from JSON Array field

Question

I have the following table with sample data:

Table:

create table tbl_jtest
(
    jdata json
);

insert into tbl_jtest values
('[{"fdate":"2021-01-01","name":"John","add1":"UK"},{"name":"Sam"},{"fdate":"2020-12-12","number":89789}]'),
('[{"name":"Jack","add2":"US","number":12302}]'),
('[{"fdate":"2011-02-11","name":"Lee"},{"number":785412}]'),
('[{"fdate":"2021-06-05","name":"Smith"},{"fdate":"2015-12-10","name":"Will"},{"name":"Jazz","number":556644}]');

I have the list of input columns which needs to be selected from JSON Array column jdata.

Given list of columns(maybe one or many):

List of columns: fdate,name,number

Expected output:

jdata
-------------------------------------------------------------------------------------------
[{"fdate":"2021-01-01","name":"John"},{"name":"Sam"},{"fdate":"2020-12-12","number":89789}]
[{"name":"Jack","number":12302}]
[{"fdate":"2011-02-11","name":"Lee"},{"number":785412}]
[{"fdate":"2021-06-05","name":"Smith"},{"fdate":"2015-12-10","name":"Will"},{"name":"Jazz","number":556644}]

Answer 1

demo:db<>fiddle

SELECT
    id,
    json_agg(new_object)                                                   -- 5
FROM (
    SELECT
        id,
        json_object_agg(elems.key, elems.value) as new_object              -- 4
    FROM tbl_jtest,
        json_array_elements(jdata) WITH ORDINALITY a_elems(value, index),  -- 1 
        json_each(a_elems.value) elems                                     -- 2
    WHERE elems.key IN ('fdate', 'name', 'number')                         -- 3
    GROUP BY id, a_elems.index
) s
GROUP BY id

You need to extract and expand the entire JSON object, to check the key strings and reaggregate the filtered records again:

1. Extract the arrays into one records per JSON object. The WITH ORDINALITY adds an index to be able to identify the correct elements later on reaggregation.
2. Extract the elements of each JSON object into one row. This creates the columns key and value
3. Filter the relevant key strings
4. Rebuild the JSON objects with the remaining key/value pairs
5. Reaggregate them into a new JSON array.