Converting Base64 To JSON And Parsing With SQL Or EsProc SPL
We have a Base64-encoded string that needs to be parsed as a table. Convert it to JSON using `FROM_BASE64` and then use `JSON_EXTRACT` to extract key-value pairs into columns. Easy with esProc SPL!
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:
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,...