Concatenate JSON array to string

Question

I have the following result, which are JSON arrays:

["elative degree of كَبِير‎ (kabīr):", "greater; greatest"]
["elative degree of كَبِير‎ (kabīr):", "bigger, larger; biggest, largest"]
["elative degree of كَبِير‎ (kabīr):", "older, elder; oldest, eldest"]
"senior (age, rank, etc.)"

Is it possible to convert these to concatenated string? e.g. for the first row:

"elative degree of كَبِير‎ (kabīr): greater; greatest"

The JSON saved in the database column looks like:

[
    {
        "glosses": [
            "elative degree of كَبِير‎ (kabīr):",
            "greater; greatest"
        ],
        "examples": [
            {
                "text": "‏اللّٰهُ أَكْبَر‎‎",
                "type": "example",
                "roman": "allāhu ʾakbar",
                "english": "God (Allah) is greater / the greatest"
            }
        ],
        "raw_glosses": [
            "greater; greatest"
        ]
    },
    {
        "glosses": [
            "elative degree of كَبِير‎ (kabīr):",
            "bigger, larger; biggest, largest"
        ],
        "raw_glosses": [
            "bigger, larger; biggest, largest"
        ]
    },
    {
        "glosses": [
            "elative degree of كَبِير‎ (kabīr):",
            "older, elder; oldest, eldest"
        ],
        "raw_glosses": [
            "older, elder; oldest, eldest"
        ]
    },
    {
        "glosses": [
            "senior (age, rank, etc.)"
        ],
        "raw_glosses": [
            "senior (age, rank, etc.)"
        ]
    }
]

score 0 · Answer 1 · answered Jun 10 '22 at 23:42

Sure, see string concatenation in python.

You can concatenate strings in python by simply:

x = 'hello there '
y = 'obi-wan kenobi'
print(x + y)

Output:

hello there obi-wan kenobi

You can extend this to your example, which I assume is a JSON array of arrays of length 2, something like:

for arr in arrays:
   print(arr[0] + arr[1])

score 0 · Accepted Answer · answered Jun 11 '22 at 12:09

This can be achieved with the following query:

SELECT id, GROUP_CONCAT( parts SEPARATOR ' ' )
FROM
(
    SELECT id, parts
    FROM dictionary
    JOIN JSON_TABLE( senses, "$[*].glosses"
    COLUMNS(
        id FOR ORDINALITY,
        NESTED PATH '$[*]' COLUMNS ( parts VARCHAR( 255 ) PATH '$' )
    ) ) AS glosses
    WHERE dictionaryId = 713481
) AS sub
GROUP BY id

Which returns a result like:

1   elative degree of كَبِير‎ (kabīr): greater; greatest
2   elative degree of كَبِير‎ (kabīr): bigger, larger; biggest, largest
3   elative degree of كَبِير‎ (kabīr): older, elder; oldest, eldest
4   senior (age, rank, etc.)

Concatenate JSON array to string

2 Answers2