* How to Parse Key-value Pairs from a Base64-encoded String in SQL?
We have a Base64-encoded string as follows:
eyJhIjo2MywiYyI6Mjk4LCJuIjoxLCJzIjoxLCJlIjo0MCwicCI6NH0=
And are trying to parse the transcoded JSON string ({"a":63,"c":298,"n":1,"s":1,"e":40,"p":4}) as a table. Below is the desired result:
a |
c |
n |
s |
e |
p |
63 |
298 |
1 |
1 |
40 |
4 |
SQL written in MySQL:
SELECT
JSON_EXTRACT(CONVERT(FROM_BASE64('eyJhIjo2MywiYyI6Mjk4LCJuIjoxLCJzIjoxLCJlIjo0MCwicCI6NH0=')
USING utf8),
'$."a"') a,
JSON_EXTRACT(CONVERT(FROM_BASE64('eyJhIjo2MywiYyI6Mjk4LCJuIjoxLCJzIjoxLCJlIjo0MCwicCI6NH0=')
USING utf8),
'$."c"') c,
JSON_EXTRACT(CONVERT(FROM_BASE64('eyJhIjo2MywiYyI6Mjk4LCJuIjoxLCJzIjoxLCJlIjo0MCwicCI6NH0=')
USING utf8),
'$."n"') n,
JSON_EXTRACT(CONVERT(FROM_BASE64('eyJhIjo2MywiYyI6Mjk4LCJuIjoxLCJzIjoxLCJlIjo0MCwicCI6NH0=')
USING utf8),
'$."s"') s,
JSON_EXTRACT(CONVERT(FROM_BASE64('eyJhIjo2MywiYyI6Mjk4LCJuIjoxLCJzIjoxLCJlIjo0MCwicCI6NH0=')
USING utf8),
'$."e"') e,
JSON_EXTRACT(CONVERT(FROM_BASE64('eyJhIjo2MywiYyI6Mjk4LCJuIjoxLCJzIjoxLCJlIjo0MCwicCI6NH0=')
USING utf8),
'$."p"') p
There is nothing hard about it. We only need to convert the Base64-encoded strings into JSON strings, then parse JSON into a table according to K-V pairs. SQL coding is complicated, particularly when the number of columns in the result table is unknown.
It is easy to code it in the open-source esProc SPL:
Suppose the value of parameter arg1 is:
eyJhIjo2MywiYyI6Mjk4LCJuIjoxLCJzIjoxLCJlIjo0MCwicCI6NH0=
A |
|
1 |
=json(base64(arg1,"UTF-8")) |
As the open-source, professional structured data computation language, SPL is convenient in handling various data sources, including JSON.
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL