How to transform multi layer sets in JSON strings with esProc

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:

https://try.esproc.com/splx?4JF

	A
1	{"Games": {"key1": "value1", "key2": value2,"key3":value3}}
2	=E@p([A1.Games])
3	=new(new(A2.(eval("new([~2]:"/ ~1 /")")):AllGames):Games)
4	=json(A3)

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.