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?
Advertisement
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.