thadguidry
commented
7 months ago If the datatype is a JSON or JSONB (like in PostgreSQL) then we should retain and return the text as represented in the DB. Also note that the JSON type is slightly different than the JSON Binary type such as:
Because the json type stores an exact copy of the input text, it will preserve semantically-insignificant white space between tokens, as well as the order of keys within JSON objects.
and
When converting textual JSON input into jsonb, the primitive types described by RFC 7159 are effectively mapped onto native PostgreSQL types, as shown in Table 8.23. Therefore, there are some minor additional constraints on what constitutes valid jsonb data that do not apply to the json type, nor to JSON in the abstract, corresponding to limits on what can be represented by the underlying data type. Notably, jsonb will reject numbers that are outside the range of the PostgreSQL numeric data type, while json will not. Such implementation-defined restrictions are permitted by RFC 7159. However, in practice such problems are far more likely to occur in other implementations, as it is common to represent JSON's number primitive type as IEEE 754 double precision floating point (which RFC 7159 explicitly anticipates and allows for). When using JSON as an interchange format with such systems, the danger of losing numeric precision compared to data originally stored by PostgreSQL should be considered.
Also note that storing JSON, and not only retrieving JSON hierarchical data can be a bit tricky and we'll want to account for test data that includes Unicode, escapes, and numeric data for full testing conformance.
Full differences of expected JSON output are documented here: https://www.postgresql.org/docs/current/datatype-json.html (I have no fucking idea about MySQL or MariaDB and could care less)
kdhrubo
Given a list of rows, it should be possible to turn this into a hierarchy based on the keys.
Refer https://blog.jooq.org/how-to-turn-a-list-of-flat-elements-into-a-hierarchy-in-java-sql-or-jooq/