Convert nested list to JSON using Python

Question

I'm using the following SQL query to get data for every month in a given year:

SELECT DATE_FORMAT(tour_date , '%M'), COUNT(*)
FROM _673338137185
WHERE tour_date LIKE '{tour_year}%'
GROUP BY DATE_FORMAT(tour_date , '%M')

When I'm returning this via Python, I'm getting the following result:

[
    [
        [
            "April",
            9
        ],
        [
            "August",
            5
        ],
        [
            "February",
            3
        ],
        [
            "July",
            6
        ],
        [
            "June",
            3
        ],
        [
            "March",
            1
        ],
        [
            "May",
            8
        ],
        [
            "November",
            1
        ],
        [
            "October",
            2
        ],
        [
            "September",
            4
        ]
    ]
]

Also, there are \n everywhere in the result. I need the result in JSON format, but I can't get it right. How can I do it?

What do you mean by there are \n everywhere? I don't see \n. This is how python format a nested list. — Corralien
– Corralien, Commented Feb 9, 2022 at 9:03

Corralien · Accepted Answer · 2022-02-09 09:05:56Z

3

If l is the list you display, simple use json.dumps:

import json

print(json.dumps(l))

# Output
[[["April", 9], ["August", 5], ["February", 3], ["July", 6], ["June", 3], ["March", 1], ["May", 8], ["November", 1], ["October", 2], ["September", 4]]]

answered Feb 9, 2022 at 9:05

Corralien

121k8 gold badges43 silver badges68 bronze badges

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

Comments

Javier · Accepted Answer · 2022-02-09 09:06:13Z

1

I believe you are pretty printing a Python list of lists.

Instead, try this:

import json

print(json.dumps(the_list_of_lists))

answered Feb 9, 2022 at 9:06

Javier

2,78718 silver badges30 bronze badges

Comments

Dharman · Accepted Answer · 2022-02-13 16:39:38Z

1

If you simply want to convert this list to a JSON you can do the following:

import json
dates = [
[
    [
        "April",
        9
    ],
    [
        "August",
        5
    ],
    [
        "February",
        3
    ],
    [
        "July",
        6
    ],
    [
        "June",
        3
    ],
    [
        "March",
        1
    ],
    [
        "May",
        8
    ],
    [
        "November",
        1
    ],
    [
        "October",
        2
    ],
    [
        "September",
        4
    ]
]
]

 dates_json = json.dumps(dates)

 print(type(dates_json)) # prints <class 'str'>

edited Feb 13, 2022 at 16:39

Dharman♦

33.9k27 gold badges106 silver badges157 bronze badges

answered Feb 9, 2022 at 9:12

omercotkd

5851 gold badge6 silver badges15 bronze badges

Comments

Rama · Accepted Answer · 2022-02-09 09:05:35Z

0

Since you are using GROUP_BY counts are grouped by month.

You can convert it into JSON like this.

import json
    data = [
    [
        [
            "April",
            9
        ],
        [
            "August",
            5
        ],
        [
            "February",
            3
        ],
        [
            "July",
            6
        ],
        [
            "June",
            3
        ],
        [
            "March",
            1
        ],
        [
            "May",
            8
        ],
        [
            "November",
            1
        ],
        [
            "October",
            2
        ],
        [
            "September",
            4
        ]
    ]
]


   new_data = [{i[0]:i[1]} for i in data[0]]

   print(json.dumps(new_data))

answered Feb 9, 2022 at 9:05

Rama

692 bronze badges

2 Comments

Peter Mortensen Over a year ago

Is that valid Python code? I get "IndentationError: unexpected indent" in Python 3.8.10 (on Ubuntu MATE 20.04).

Rama Over a year ago

You have to format it properly.

Peter Mortensen · Accepted Answer · 2022-02-14 16:40:35Z

0

I have a similar problem. I solved it using jsonpickle. I expect it to be as easy as the following line:

jsonObject = jsonpickle.encode(obj)

edited Feb 14, 2022 at 16:40

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Feb 9, 2022 at 9:06

Sven

414 bronze badges

Collectives™ on Stack Overflow

Convert nested list to JSON using Python

5 Answers 5

Comments

Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related