Hi all 👋
I've recently run into an issue with JSON_EXTRACT_SCALAR. The issue being that you cannot have a subquery, or concatenated string within the JSON path.
CASE WHEN JSON_EXTRACT_SCALAR(children, '$[0][children][30][name]') = 'AAH' THEN JSON_EXTRACT_SCALAR(children, '$[0][children][30][data][annualisedAdvance]')
WHEN JSON_EXTRACT_SCALAR(children, '$[0][children][29][name]') = 'AAH' THEN JSON_EXTRACT_SCALAR(children, '$[0][children][29][data][annualisedAdvance]')
WHEN JSON_EXTRACT_SCALAR(children, '$[0][children][28][name]') = 'AAH' THEN JSON_EXTRACT_SCALAR(children, '$[0][children][28][data][annualisedAdvance]')
WHEN JSON_EXTRACT_SCALAR(children, '$[0][children][27][name]') = 'AAH' THEN JSON_EXTRACT_SCALAR(children, '$[0][children][27][data][annualisedAdvance]')
WHEN JSON_EXTRACT_SCALAR(children, '$[0][children][26][name]') = 'AAH' THEN JSON_EXTRACT_SCALAR(children, '$[0][children][26][data][annualisedAdvance]')
WHEN JSON_EXTRACT_SCALAR(children, '$[0][children][25][name]') = 'AAH' THEN JSON_EXTRACT_SCALAR(children, '$[0][children][25][data][annualisedAdvance]')
WHEN JSON_EXTRACT_SCALAR(children, '$[0][children][24][name]') = 'AAH' THEN JSON_EXTRACT_SCALAR(children, '$[0][children][24][data][annualisedAdvance]')
WHEN JSON_EXTRACT_SCALAR(children, '$[0][children][23][name]') = 'AAH' THEN JSON_EXTRACT_SCALAR(children, '$[0][children][23][data][annualisedAdvance]')
I'm wanting to squash the above from 30 lines of code into 4 lines by doing the following:
{% set max_children = 30 %}
CASE {% for num in range(max_children)|sort(reverse=true) %}
WHEN JSON_EXTRACT(children, CONCAT('$[0][children][','{{num}}','][name]')) = 'AAH'
THEN JSON_EXTRACT(children, CONCAT('$[0][children][','{{num}}','][data][annualisedAdvance]'))
{% endfor %} END AS aad_1,
Above uses jinja templating. However this relies on using CONCAT within the JSON path.
Just wondering if there are any plans to allow CONCAT in the JSON path? Or what the basis is for not allowing currently.
Thanks,
Will
LinkedIn
Twitter
