0 
    query = "SELECT key, json_agg(distinct value) as values FROM {}, jsonb_each(to_jsonb({})) GROUP BY key"

I have this above sql query to fetch data from postgres database. This query works fine in postgres database.

Is there any similar query to make it

    mysql> select * from soubhagyairisdata;
    +-------+------+---------------+--------------+------------+--------------+-------------+
    | index | id   | sepallengthcm | sepalwidthcm | unnamed:_3 | petalwidthcm | species     |
    +-------+------+---------------+--------------+------------+--------------+-------------+
    |     0 |   47 |           5.1 |          3.8 |        1.6 |          0.2 | setosa      |
    |     1 |   48 |           4.6 |          3.2 |        1.4 |          0.2 | setosa      |
    |     2 |   49 |           5.3 |          3.7 |        1.5 |          0.2 | jennifer    |
    |     3 |   50 |             5 |          3.3 |        1.4 |          0.2 | setosa      |
    |     4 |   97 |            12 |          2.9 |        4.2 |          1.3 | jennifer    |
    |     5 |   98 |           6.2 |          2.9 |        4.3 |          1.3 | jennifer    |
    |     6 |   99 |           5.1 |          2.5 |          3 |          1.1 | kajol       |
    |     7 |  100 |            11 |          2.8 |          7 |          1.3 | floaw       |
    |     8 |  101 |           6.3 |          3.3 |          6 |          2.5 | Iris-flower |
    |     9 |  102 |           5.8 |          2.7 |        5.1 |          1.9 | Iris-flower |
    +-------+------+---------------+--------------+------------+--------------+-------------+

Here is my sample data

    mysql> SELECT JSON_ARRAYAGG(species) FROM soubhagyairisdata;
    +--------------------------------------------------------------------------------------------------------------------+
    | JSON_ARRAYAGG(species)                                                                                             |
    +--------------------------------------------------------------------------------------------------------------------+
    | ["setosa", "setosa", "jennifer", "setosa", "jennifer", "jennifer", "kajol", "floaw", "Iris-flower", "Iris-flower"] |
    +--------------------------------------------------------------------------------------------------------------------+

I need these for all columns witlout passing column name

Improve this question
5
  • what do you mean without passing column name? then how you know which columns needs to be used to create the json string? Commented Feb 1, 2021 at 16:11
  • Please check my postgres query i shared in question. There i am not passing any column name Commented Feb 1, 2021 at 16:12
  • is that query valid? to me seems like that query won't run at all. Commented Feb 1, 2021 at 16:27
  • That works on postgres shell Commented Feb 1, 2021 at 16:40
  • I doubt it works , I even ran it in shell to make sure Commented Feb 1, 2021 at 19:13

2 Answers 2

Reset to default
3

yes,

you can use JSON_OBJECTAGG(key, value) or JSON_ARRAYAGG(col_or_expr)

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

If i don't know the column names how can i get that ? Can you please write full query ?
please provide sample data and desired output to your question plus maybe table structure
Updated question please check
3

Hello if you need write all query in json structure in mariadb or mysql you need create all column to pair key and value how this

select JSON_ARRAYAGG(DISTINCT JSON_OBJECT( 'id', tmu.id, 'name', tmu.name, 'orders', case when json_orders is null then '[]' else json_orders end,'pool', o2.idpool ) ) as json

Same this example. i hope help you see ya.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.