Baseencoded 문자열에서 키-값 쌍을 구문 분석하는 방법#eg11-MySQL 튜토리얼-php.cn

Baseencoded 문자열에서 키-값 쌍을 구문 분석하는 방법#eg11

王林

풀어 주다： 2024-07-18 06:58:30

원래의

350명이 탐색했습니다.

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:

Image description
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=

Image description
As the open-source, professional structured data computation language, SPL is convenient in handling various data sources, including JSON.