Joining tables with filter condition on multiple columns in Oracle DBMS

Here is the best I can do with your JSON input and your sample output.

Note that your document has a unique "id" and "name" ("1" and "test" in your example). Then it has an array named "productSpecificationRelationship". Each element of this array is an object with its own "id" - in the query, I show this id with the column name PSR_ID (PSR for Product Specification Relationship). Also, each object in this first-level array contains a sub-array (second level), with objects with "name" ("name" again!) and "value" keys. (This looks very much like an entity-attribute-value model - very poor practice.) In the intermediate step in my query (before pivoting), I call these RC_NAME and RC_VALUE (RC for Relationship Characteristic).

In your sample output you have more than one value in the ID and NAME columns. I don't see how that is possible; perhaps from unpacking more than one document? The JSON document you shared with us has "id" and "name" as top-level attributes.

In the output, I understand (or rather, assume, since I didn't understand too much from your question) that you should also include the PSR_ID - there is only one in your document, with value "10499", but in principle there may be more than one, and the output will have one row per such id.

Also, I assume the "name" values are limited to the four you mentioned (or, if there can be more, you are only interested in those four in the output).

With all that said, here is the query. Note that I called the table ES for simplicity. Also, you will see that I had to go to nested path twice (since your document includes an array of arrays, and I wanted to pick up the PSR_ID from the outer array and the tokens from the nested arrays).

TABLE SETUP

create table es (payloadentityspecification clob 
                 check (payloadentityspecification is json) );                  
insert into es (payloadentityspecification) values (
'{
  "description": "test",
  "id": "1",
  "name": "test",
  "productSpecificationRelationship": [
    {
      "id": "10499",
      "relationshipType": "channelRelation",
      "relationshipCharacteristic": [
        {
          "name": "out_of_home",
          "value": "1"
        },
        {
          "name": "out_of_home_ios",
          "value": "1" 
        },
        {
          "name": "out_of_home_android",
          "value": "0"
        },
        {
          "name": "out_of_home_web",
          "value": "0"
        }
      ]
     }
  ]
}');
commit;

QUERY

with
  prep (id, name, psr_id, rc_name, rc_value) as (
    select id, name, psr_id, rc_name, rc_value
    from   es,
           json_table(payloadentityspecification, '$'
                      columns (
                                id   varchar2(10) path '$.id',
                                name varchar2(40) path '$.name',
                          nested path '$.productSpecificationRelationship[*]'
                          columns (
                                    psr_id varchar2(10) path '$.id',
                              nested path '$.relationshipCharacteristic[*]'
                              columns (
                                        rc_name  varchar2(50) path '$.name',
                                        rc_value varchar2(50) path '$.value'
                              )         
                          )
                      )
           )
  )
select id, name, psr_id, ooh, ooh_android, ooh_ios, ooh_web
from   prep
pivot ( min(case rc_value when '1' then 'TRUE' 
                          when '0' then 'FALSE' else 'UNDEFINED' end)
        for rc_name in ( 'out_of_home'         as ooh,
                         'out_of_home_android' as ooh_android,
                         'out_of_home_ios'     as ooh_ios,
                         'out_of_home_web'     as ooh_web
                       )
      )
;

OUTPUT

ID NAME PSR_ID OOH         OOH_ANDROID OOH_IOS     OOH_WEB  
-- ---- ------ ----------- ----------- ----------- -----------
1  test 10499  TRUE        FALSE       TRUE        FALSE  

Conditional aggregation might be used in order to pivot the result set after extracting the values by using JSON_TABLE() and JSON_VALUE() functions such as

 SELECT JSON_VALUE(payloadentityspecification, '$.name') AS channel_map_name,
        MAX(CASE WHEN name = 'out_of_home' THEN 
                      DECODE(value,1,'TRUE',0,'FALSE','UNDEFINED')
                  END) AS ooh,
        MAX(CASE WHEN name = 'out_of_home_android' THEN 
                      DECODE(value,1,'TRUE',0,'FALSE','UNDEFINED')
                  END) AS ooh_android,
        MAX(CASE WHEN name = 'out_of_home_ios' THEN 
                      DECODE(value,1,'TRUE',0,'FALSE','UNDEFINED')
                  END) AS ooh_ios,
        MAX(CASE WHEN name = 'out_of_home_web' THEN 
                      DECODE(value,1,'TRUE',0,'FALSE','UNDEFINED')
                  END) AS ooh_web                  
   FROM EntitySpecification ES,
   JSON_TABLE (payloadentityspecification, '$.productSpecificationRelationship[*]'
                    COLUMNS (  NESTED PATH '$.relationshipCharacteristic[*]'
                    COLUMNS (                
                             description VARCHAR2(250) PATH '$.description',
                             name        VARCHAR2(250) PATH '$.name',
                             value       VARCHAR2(250) PATH '$.value'
                            )
   )) jt
  WHERE payloadentityspecification IS JSON
 GROUP BY JSON_VALUE(payloadentityspecification, '$.name')

Demo