Display the (distinct) keys from a JSONB column that matches certain condition(s)-CodePudding

I have a PostgreSQL 12.x database. There is a column data in a table typename that contains jsonb values. The actual JSON data is not fixed to a particular structure; these are some examples:

{"emt": {"key": " ", "source": "INPUT"}, "id": 1, "fields": {}}

{"emt": {"key": "Stack Overflow", "source": "INPUT"}, "id": 2, "fields": {}}

{"emt": {"key": "https://www.domain.tld/index.html", "source": "INPUT"}, "description": {"key": "JSONB datatype", "source": "INPUT"}, "overlay": {"id": 5, "source": "bOv"}, "fields": {"id": 1, "description": "Themed", "recs ": "1"}}

What I'm trying to do is to get all the JSON keys bound to objects that:

Contain only two elements: key and source
source element must be bound to "INPUT"

Basically, for this example, the result should be: emt, description.

This is what I have so far, but it's not quite working as expected:

select distinct jsonb_object_keys(data) as keys
from typename
where jsonb_path_exists(data, '$.** ? (@.type() == "string" && @ like_regex "INPUT")');
-- where jsonb_typeof(data -> ???) = 'object'
--   and jsonb_path_exists(data, '$.???.key ? (@.type() == "string")')
--   and jsonb_path_exists(data, '$.???.source ? (@.type() == "string" && @ like_regex "INPUT")');

CodePudding user response：

I write for you sample, maybe you will need:

with tbl as (
select '{"emt": {"key": " ", "source": "INPUT"}, "id": 1, "fields": {}}'::jsonb as jsondata
union all 
select '{"emt": {"key": "Stack Overflow", "source": "INPUT"}, "id": 2, "fields": {}}'::jsonb
union all 
select '{"emt": {"key": "https://www.domain.tld/index.html", "source": "INPUT"}, "description": {"key": "JSONB datatype", "source": "INPUT"}, "overlay": {"id": 5, "source": "bOv"}, "fields": {"id": 1, "description": "Themed", "recs ": "1"}}'::jsonb
)
select 
    jsondata->'emt' as emt, 
    jsondata->'description' as description 
from 
    tbl
where 
    jsondata->'emt'->>'source' = 'INPUT'

Result: 
|---------------------------------------------------------------- --------------------------------------------- 
| emt                                                            | description                                 |
|---------------------------------------------------------------- --------------------------------------------- 
| {"key": " ", "source": "INPUT"}                                |                                             |
| {"key": "Stack Overflow", "source": "INPUT"}                   |                                             |
| {"key": "https://www.domain.tld/index.html", "source": "INPUT"}| {"key": "JSONB datatype", "source": "INPUT"}|

CodePudding user response：

Some samples maybe need to you:

Sample1:

with tbl as (
select '{"emt": {"key": " ", "source": "INPUT"}, "id": 1, "fields": {}}'::jsonb as jsondata
union all 
select '{"emt": {"key": "Stack Overflow", "source": "INPUT"}, "id": 2, "fields": {}}'::jsonb
union all 
select '{"emt": {"key": "https://www.domain.tld/index.html", "source": "INPUT"}, "description": {"key": "JSONB datatype", "source": "INPUT"}, "overlay": {"id": 5, "source": "bOv"}, "fields": {"id": 1, "description": "Themed", "recs ": "1"}}'::jsonb
)
select distinct 
    jsonb_object_keys(jsondata->'emt') as keys
from 
    tbl
where 
    jsondata->'emt'->>'source' = 'INPUT'

Result: 
| keys  |
|-------|
| key   |
| source|

Sample2:

with tbl as (
select '{"emt": {"key": " ", "source": "INPUT"}, "id": 1, "fields": {}}'::jsonb as jsondata
union all 
select '{"emt": {"key": "Stack Overflow", "source": "INPUT"}, "id": 2, "fields": {}}'::jsonb
union all 
select '{"emt": {"key": "https://www.domain.tld/index.html", "source": "INPUT"}, "description": {"key": "JSONB datatype", "source": "INPUT"}, "overlay": {"id": 5, "source": "bOv"}, "fields": {"id": 1, "description": "Themed", "recs ": "1"}}'::jsonb
)
select 
    count(distinct t1.keys) as emt_count, 
    count(distinct t1.descs) as desc_count 
from (
    select 
        jsonb_object_keys(jsondata->'emt') keys,
        jsonb_object_keys(jsondata->'description') descs
    from 
        tbl
    where 
        jsondata->'emt'->>'source' = 'INPUT'
) t1     

Result:
| emt_count | desc_count |
|----------- ------------|
|         2 |          2 |

Sample3:

with tbl as (
select '{"emt": {"key": " ", "source": "INPUT"}, "id": 1, "fields": {}}'::jsonb as jsondata
union all 
select '{"emt": {"key": "Stack Overflow", "source": "INPUT"}, "id": 2, "fields": {}}'::jsonb
union all 
select '{"emt": {"key": "https://www.domain.tld/index.html", "source": "INPUT"}, "description": {"key": "JSONB datatype", "source": "INPUT"}, "overlay": {"id": 5, "source": "bOv"}, "fields": {"id": 1, "description": "Themed", "recs ": "1"}}'::jsonb
)
select  
    jsondata->'emt'->>'key' as key,
    jsondata->'emt'->>'source' as source, 
    jsondata->'description'->>'key' as desc_key, 
    jsondata->'description'->>'source' as desc_source
from 
    tbl
where 
    jsondata->'emt'->>'source' = 'INPUT'

Result:     
| key                              | source | desc_key       | desc_source |
|---------------------------------- -------- ---------------- ------------- 
|                                  | input  |                |             |
| Stack Overflow                   | INPUT  |                |             |
| https://www.domain.tld/index.html| input  | JSONB datatype | INPUT       |