Problem with a SELECT

Question

Problem with a SELECT

Navigation

#1 by (6 votes)

0

I have the following SELECT generated by a JSON array with objects that are hotels.

    SELECT CONCAT(
    '[', 
    GROUP_CONCAT(JSON_OBJECT('id', h.hotel_id , 'nom', h.nom, 'estrelles', h.categoria, 'puntuacio', h.puntacio, 'tipus', h.tipus, 'descripcio', h.descripcio, 'adreca', h.adreca, 'lat', h.latitud, 'lng', h.longitud, 'web', h.web )),
    ']'
) 
FROM hotels h

The problem is that it only generates some hotels, not all of them.

[
{
    "id": 1,
    "lat": 41.39630126953125,
    "lng": 2.166100025177002,
    "nom": "987 Barcelona",
    "web": "room-matehotels.com",
    "tipus": "H",
    "adreca": "Mallorca, 288",
    "estrelles": 4,
    "puntuacio": 4,
    "descripcio": "Sus instalaciones constan de 1 bar, ascensores, terraza, recepción 24 h, cambio de moneda, servicio de lavandería, de niñera, una sala de conferencias de hasta 8 personas o servicio para minusválidos."
},
{
    "id": 2,
    "lat": 41.375999450683594,
    "lng": 2.1670000553131104,
    "nom": "Acta Millenni ",
    "web": " hotel-millennibarcelona.com",
    "tipus": "H",
    "adreca": "Ronda de Sant Pau, 14",
    "estrelles": 4,
    "puntuacio": 3,
    "descripcio": "A prop del centre de la ciutat i de la platja; botigues, llocs turístics, restaurants i bars a prop."
},
{
    "id": 3,
    "lat": 41.37900161743164,
    "lng": 2.169600009918213,
    "nom": "Barcelo Raval",
    "web": "barcelo.com",
    "tipus": "H",
    "adreca": "Rambla del Raval, 17-21",
    "estrelles": 4,
    "puntuacio": 4,
    "descripcio": "Mol bon hotel, bones habitacions i serveis.."
},
{
    "id": 4,
    "lat": 41.375198364257
]

As you can see in the last object it is half-way to generate it.

Any idea why he does this?

mysql query select

asked by Mohamed A.B 16.03.2018 в 11:03

source

1 answer

How to define a list of many elements How to replace spaces in String with underscores?

score 6 · Accepted Answer

It looks like the problem is limiting the characters of the GROUP_CONCAT function. It has a default limitation of 1024 characters. You will have to change the value:

SET SESSION group_concat_max_len = 1000000;

Here more info:

link

and here:

link

Starting with MySQL 5.7.22 you have available the JSON_ARRAYAGG function that you can use instead of GROUP_CONCAT :

SELECT
  JSON_ARRAYAGG(
    JSON_OBJECT(
      'id', h.hotel_id ,
      'nom', h.nom,
      'estrelles', h.categoria,
      'puntuacio', h.puntacio,
      'tipus', h.tipus,
      'descripcio', h.descripcio,
      'adreca', h.adreca,
      'lat', h.latitud,
      'lng', h.longitud,
      'web', h.web
    )
  ) json
FROM hotels h

(View online demo)