Editing the output (set brackets depending on the quantity retrieved and the semicolon)

Every time that I run my code I obtain one, or two or several rows on a JSON format. I am using Oracle 11g.

This is my CODE on SQL Fiddle, you will find all the data there.

For example, in this case I obtained something like this:

{“sku”:”99342435″,”PRICE”:”9999″,PRICES_FOR_CLIENTS:[{“group”:”A”,”PRICE”:”29223″},{“group”:”B”,”PRICE”:”33223″},{“group”:”SUPERMARKET”,”PRICE”:”48343″},{“group”:”WALMART”,”PRICE”:”40340″}]};

BUT I could get this output depending on the data stored in the tables:

{“sku”:”99342435″,”PRICE”:”9999″,PRICES_FOR_CLIENTS:[{“group”:”A”,”PRICE”:”29223″},{“group”:”B”,”PRICE”:”33223″},{“group”:”SUPERMARKET”,”PRICE”:”48343″},{“group”:”WALMART”,”PRICE”:”40340″}]};

{“sku”:”95453343″,”PRICE”:”8778″,PRICES_FOR_CLIENTS:[{“group”:”A”,”PRICE”:”29223″},{“group”:”B”,”PRICE”:”33223″},{“group”:”SUPERMARKET”,”PRICE”:”48343″},{“group”:”WALMART”,”PRICE”:”40340″}]};

I want to make my query able to put an “open bracket” in the first row, check if there exist another rows and if no new rows are shown then, insert a close bracket in the end and a semicolon; something like this:

[{“sku”:”99342435″,”PRICE”:”9999″,PRICES_FOR_CLIENTS:[{“group”:”A”,”PRICE”:”29223″},{“group”:”B”,”PRICE”:”33223″},{“group”:”SUPERMARKET”,”PRICE”:”48343″},{“group”:”WALMART”,”PRICE”:”40340″}]}];

BUT another scenario could be when there exist more than 1 row; in that case I would like to put an open bracket in the first row but not a close bracket. I only want to open the bracket in the first row and close it in the last row and within, a semicolon. The different rows MUST BE separated by a comma. Please, follow this example:

[{“sku”:”99342435″,”PRICE”:”9999″,PRICES_FOR_CLIENTS:[{“group”:”A”,”PRICE”:”29223″},{“group”:”B”,”PRICE”:”33223″},{“group”:”SUPERMARKET”,”PRICE”:”48343″},{“group”:”WALMART”,”PRICE”:”40340″}]},

{“sku”:”95453343″,”PRICE”:”8778″,PRICES_FOR_CLIENTS:[{“group”:”A”,”PRICE”:”29223″},{“group”:”B”,”PRICE”:”33223″},{“group”:”SUPERMARKET”,”PRICE”:”48343″},{“group”:”WALMART”,”PRICE”:”40340″}]}];

I tried so many times but I was not able to do it.

Can you help me?

Answer

This is starting to fall into the realm of just because you can make it work, doesn’t mean you should. But to answer your question, check this code out:

SELECT CASE WHEN sub2.TOTAL_ROW > 1 AND sub2.this_row = 1 THEN '[' ELSE NULL END||
       sub2.json||
       CASE WHEN sub2.total_row > 1 AND sub2.this_row = sub2.total_row THEN ']' ELSE NULL END AS JSON
FROM (SELECT '{"sku":"'||sub.item_code||'","PRICE":"'||sub.item_price||'",PRICES_FOR_CLIENTS:['||listagg('{"group":"'||sub.identifier||'","PRICE":"'||sub.price||'"}',',') WITHIN GROUP (ORDER BY sub.identifier)||']};' AS JSON, 
      COUNT(*) OVER () AS TOTAL_ROW,
      ROW_NUMBER() OVER (ORDER BY sub.item_code, sub.item_price) AS THIS_ROW                                         
      FROM (SELECT DECODE(ppc.customer_class_code, 'E', c.description, ppc.customer_class_code) AS IDENTIFIER, tpp.item_code, replace(tpp.price, ',', '.') AS ITEM_PRICE, REPLACE(avg(ppc.price), ',', '.') AS PRICE, 
            tpl.request_id, max(tpl.request_id) over (partition by tpp.item_code) as max_request
            FROM table_price_list tpl
            INNER JOIN table_price_product tpp ON tpp.list_header_id = tpl.list_header_id AND tpp.request_id = tpl.request_id
            INNER JOIN prices_per_client ppc ON tpp.item_code = ppc.item_code
            LEFT JOIN clients c ON ppc.customer_number = c.account_number
            WHERE SYSDATE BETWEEN NVL(tpp.start_date_active, SYSDATE) AND NVL(tpp.end_date_active, SYSDATE+1)
            GROUP BY DECODE(ppc.customer_class_code, 'E', c.description, ppc.customer_class_code), tpp.item_code, tpp.price, tpl.request_id) sub 
      WHERE sub.identifier IS NOT NULL
      and sub.request_id = sub.max_request
      GROUP BY sub.item_code, sub.item_price) sub2
ORDER BY sub2.this_row;

I updated your SQLFiddle

What this query does is expand on the previous query. It counts the total rows and this row using the analytic functions COUNT and ROW_NUMBER. I didn’t partition them at all because we want them to consider all returned rows. If Total Rows > 1 and this is the first row start it with ‘[‘. Append the JSON in the middle. And if this is the last row (Total > 1 and ROW_NUMBER = COUNT) then append ‘]’ to the end.

Edit 1: Changed to always have brackets

SELECT DECODE(sub2.this_row, 1, '[', NULL)||
       sub2.json||
       DECODE(sub2.this_row, sub2.total_row, ']', NULL) AS JSON
FROM (SELECT '{"sku":"'||sub.item_code||'","PRICE":"'||sub.item_price||'",PRICES_FOR_CLIENTS:['||listagg('{"group":"'||sub.identifier||'","PRICE":"'||sub.price||'"}',',') WITHIN GROUP (ORDER BY sub.identifier)||']};' AS JSON, 
      COUNT(*) OVER () AS TOTAL_ROW,
      ROW_NUMBER() OVER (ORDER BY sub.item_code, sub.item_price) AS THIS_ROW                                         
      FROM (SELECT DECODE(ppc.customer_class_code, 'E', c.description, ppc.customer_class_code) AS IDENTIFIER, tpp.item_code, replace(tpp.price, ',', '.') AS ITEM_PRICE, REPLACE(avg(ppc.price), ',', '.') AS PRICE, 
            tpl.request_id, max(tpl.request_id) over (partition by tpp.item_code) as max_request
            FROM table_price_list tpl
            INNER JOIN table_price_product tpp ON tpp.list_header_id = tpl.list_header_id AND tpp.request_id = tpl.request_id
            INNER JOIN prices_per_client ppc ON tpp.item_code = ppc.item_code
            LEFT JOIN clients c ON ppc.customer_number = c.account_number
            WHERE SYSDATE BETWEEN NVL(tpp.start_date_active, SYSDATE) AND NVL(tpp.end_date_active, SYSDATE+1)
            GROUP BY DECODE(ppc.customer_class_code, 'E', c.description, ppc.customer_class_code), tpp.item_code, tpp.price, tpl.request_id) sub 
      WHERE sub.identifier IS NOT NULL
      and sub.request_id = sub.max_request
      GROUP BY sub.item_code, sub.item_price) sub2
ORDER BY sub2.this_row;

Here is the updated SQLFiddle (Link).

Given the simpler logic, I switched out the CASE for DECODE. IF the row is the first row it gets the opening bracket. If it is the last row, it gets the closing bracket. If it is both, it gets both.