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}]

@S-Man, add1 and add2 columns are there in JSON array please check. Which should not be in output. — MAK
– MAK, Commented Mar 10, 2021 at 11:30

S-Man · Accepted Answer · 2021-03-10 11:43:47Z

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:

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.
Extract the elements of each JSON object into one row. This creates the columns key and value
Filter the relevant key strings
Rebuild the JSON objects with the remaining key/value pairs
Reaggregate them into a new JSON array.

Collectives™ on Stack Overflow

Get selected list of columns from JSON Array field

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related