Select query using json format value

Question

If customer first_name-'Monika', last_name='Awasthi'

Then I am using below query to return value in json format:

SELECT * 
  FROM
  (
   SELECT JSON_ARRAYAGG(JSON_OBJECT('CODE' IS '1','VALUE' IS 'Monika'||' '||'Awasthi'))
     FROM DUAL 
  );

It is working fine & give below output:

[{"CODE":"1","VALUE":"Monika Awasthi"}]

But I want one more value which should be reversed means output should be:

[{"CODE":"1","VALUE":"Monika Awasthi"},{"CODE":"2","VALUE":"Awasthi Monika"}]

Kindly give me some suggestions. Thank You

The question is not entirely clear. If I understand correctly, you have a table customer with columns first_name and last_name and you are creating a JSON string. OK - where do the "codes" come from? I understand 1 and 2 if your table has a single row; but if it has 300 rows, what "codes" do you assign? By what rule? — user5683823
– user5683823, Commented Feb 20, 2021 at 14:53

Alex Poole · Accepted Answer · 2021-02-20 11:43:06Z

2

Another approach is to use a CTE to generate the two codes and values; your original version could be written to get the name data from a table or CTE:

-- CTE for sample data
WITH cte (first_name, last_name) AS (
  SELECT 'Monika', 'Awasthi' FROM DUAL
)
-- query against CTE or table
SELECT JSON_ARRAYAGG(JSON_OBJECT('CODE' IS '1','VALUE' IS last_name ||' '|| first_name))
FROM cte;

And you could then extend that with a CTE that generates the value with the names in both orders:

WITH cte1 (first_name, last_name) AS (
  SELECT 'Monika', 'Awasthi' FROM DUAL
),
cte2 (code, value) AS (
  SELECT 1 AS code, first_name || ' ' || last_name FROM cte1
  UNION ALL
  SELECT 2 AS code, last_name || ' ' || first_name FROM cte1
)
SELECT JSON_ARRAYAGG(JSON_OBJECT('CODE' IS code,'VALUE' IS value))
FROM cte2;

which gives:

JSON_ARRAYAGG(JSON_OBJECT('CODE'ISCODE,'VALUE'ISVALUE))
-------------------------------------------------------------------------
[{"CODE":1,"VALUE":"Monika Awasthi"},{"CODE":2,"VALUE":"Awasthi Monika"}]

db<>fiddle

answered Feb 20, 2021 at 11:43

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

monika Over a year ago

Thanks @Alex Poole- Your answer fulfill my requirement.:)

Barbaros Özhan · Accepted Answer · 2021-02-20 14:50:55Z

2

A simple logic through use of SQL(without using PL/SQL) in order to generate code values as only be usable for two columns as in this case might be

SELECT JSON_ARRAYAGG(
          JSON_OBJECT('CODE' IS tt.column_id,
                      'VALUE' IS CASE WHEN column_id=1 
                                      THEN name||' '||surname
                                      ELSE surname||' '||name
                                       END)
                    ) AS result
  FROM t
 CROSS JOIN (SELECT column_id FROM user_tab_cols WHERE table_name =  'T') tt

where t is a table which hold name and surname columns

Demo

More resilient solution might be provided through use of PL/SQL, even more columns exist within the data source such as

DECLARE
 v_jso   VARCHAR2(4000);
 v_arr   OWA.VC_ARR; 
 v_arr_t JSON_ARRAY_T := JSON_ARRAY_T(); 
BEGIN
 FOR c IN ( SELECT column_id FROM user_tab_cols WHERE table_name = 'T' )
 LOOP
   SELECT 'JSON_OBJECT( ''CODE'' IS '||MAX(c.column_id)||',
                        ''VALUE'' IS '||LISTAGG(column_name,'||'' ''||') 
                                        WITHIN GROUP (ORDER BY ABS(column_id-c.column_id))
                                        ||' )'
     INTO v_arr(c.column_id)                                   
     FROM ( SELECT * FROM user_tab_cols WHERE table_name = 'T' );

   EXECUTE IMMEDIATE 'SELECT '||v_arr(c.column_id)||' FROM t' INTO v_jso;
   v_arr_t.APPEND(JSON_OBJECT_T(v_jso));
 END LOOP;

 DBMS_OUTPUT.PUT_LINE(v_arr_t.STRINGIFY);  
 
END;
/

Demo

edited Feb 20, 2021 at 14:50

answered Feb 20, 2021 at 11:33

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

2 Comments

Alex Poole Over a year ago

Neat, but assumes the table only has two columns. You could restrict that, but the generation of the two code values doesn't need to rely on that view, it could be a union again dual, or an xmltable call (quick demo).

Barbaros Özhan Over a year ago

you're right exactly @AlexPoole as I mentioned. Perhaps using dynamic SQL through use of PL/SQL with more brainstorm might make it more resilient.

user5683823 · Accepted Answer · 2021-02-20 14:59:55Z

As I explained in a comment under your question, I am not clear on how you define the CODE values for your JSON string (assuming you have more than one customer).

Other than that, if you need to create a JSON array of objects from individual strings (as in your attempt), you probably need to use JSON_ARRAY rather than JSON_ARRAYAGG. Something like I show below. Incidentally, I also don't know why you needed to SELECT * FROM (subquery) - the outer SELECT seems entirely unnecessary.

So, if you don't actually aggregate over a table, but just need to build a JSON array from individual pieces:

select json_array
       (
         json_object('CODE' is '1', 'VALUE' is first_name || ' ' || last_name ),
         json_object('CODE' is '2', 'VALUE' is last_name  || ' ' || first_name)
       ) as result
from   ( select 'Monika' as first_name, 'Awasthi' as last_name from dual )
;

RESULT                                                                        
------------------------------------------------------------------------------
[{"CODE":"1","VALUE":"Monika Awasthi"},{"CODE":"2","VALUE":"Awasthi Monika"}]

Collectives™ on Stack Overflow

Select query using json format value

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related