How to quickly transform multi-layer sets in JSON strings

There is a 2-layer JSON string with multiple dynamic key values in the lower layer, excluding set/array types. {"Games": {"key1": "value1", "key2": value2,"key3":value3}} Now we need to transform the lower layer into a set of multiple layers. {"Games":{"AllGames":[{"key1":["value1"]},{"key2":["value2"]},{"key3":["value3"]}]}} SQL itself does not support multi-layer data, making it more difficult to express multi-layer sets, and the indirectly implemented code is very complex. SPL naturally supports multi-layer data and multi-layer sets: Try.DEMO A1: Automatically parse the multi-layer JSON string into SPL table sequence. After clicking and expanding, you can see that the upper layer is a single field record, and the lower layer is a multi-field record. A2: Take the lower-layer data (one record), change the type to a set of records, and then transpose it to a two-layer sequence. […] represents a set, function E can convert a set of records into a two-dimensional sequence, and @ p represents transposition during conversion. A3 code: A2.(eval(“new([~2]:”/ ~1 /“)”)) Convert the sequence in A2 into a multi-layer set. The eval function can execute strings as code, while the new function (field value: field name) can generate a record, where ~ represents the current member of the sequence and ~1 represents the first sub-member. The calculation results are shown in the following figure: new(new(…:AllGames):Games) Add two layers of record types on top of a multi-layer set to construct the target table sequence. A4: Finally, use the JSON function to convert the table sequence into the target JSON string. esProc is free. Download~~

May 7, 2025 - 09:38

How to quickly transform multi-layer sets in JSON strings

There is a 2-layer JSON string with multiple dynamic key values in the lower layer, excluding set/array types.

{"Games": {"key1": "value1", "key2": value2,"key3":value3}}

Now we need to transform the lower layer into a set of multiple layers.

{"Games":{"AllGames":[{"key1":["value1"]},{"key2":["value2"]},{"key3":["value3"]}]}}

SQL itself does not support multi-layer data, making it more difficult to express multi-layer sets, and the indirectly implemented code is very complex. SPL naturally supports multi-layer data and multi-layer sets:

Try.DEMO

A1: Automatically parse the multi-layer JSON string into SPL table sequence. After clicking and expanding, you can see that the upper layer is a single field record, and the lower layer is a multi-field record.

A2: Take the lower-layer data (one record), change the type to a set of records, and then transpose it to a two-layer sequence. […] represents a set, function E can convert a set of records into a two-dimensional sequence, and @ p represents transposition during conversion.

A3 code: A2.(eval(“new([~2]:”/ ~1 /“)”)) Convert the sequence in A2 into a multi-layer set. The eval function can execute strings as code, while the new function (field value: field name) can generate a record, where ~ represents the current member of the sequence and ~1 represents the first sub-member. The calculation results are shown in the following figure: