Не понимаю как правильно ли использую функцию DISTINCT?

Question

[holllop]

У меня есть таблица в ней 2 столбца в них вот такие вот данные:

"Принтер ч/б" "A3"
"Принтер ч/б" "A4"
"Принтер цвет" "610*175m"
"Принтер цвет" "610*50m"
"Принтер цвет" "914*175m"
"Принтер цвет" "914*50m"
"Принтер цвет" "A3"
"Принтер цвет" "A4"
"Принтер цвет" "A3color"
"Принтер цвет" "A4color"
"Принтер ч/б и цвет" "610*175m"
"Принтер ч/б и цвет" "610*175m"
"Принтер ч/б и цвет" "914*175m"
"Принтер ч/б и цвет" "914*50m"

И как вы видите в левом столбце часто идут повторения, и я этого хотел бы избежать, для этого я использовал команду

SELECT DISTINCT printer, materials FROM printer_materials

, но не достиг желаемого результата. Желаемый результат это использовать похожую команду на стороне сервера чтобы получить такие данные в JSON формате по ссылке:

[
{
printer: "Принтер ч/б"
    {
    materials: "A3",
    materials: "A4"
    }
},
{
printer: "Принтер цвет"
    {
    materials: "610*175m",
    materials: "610*50m",
    materials: "914*175m",
    materials: "914*50m",
    materials: "A3",
    materials: "A4",
    materials: "A3color",
    materials: "A4color"
    }
},   
{
printer: "Принтер ч/б и цвет",
    {
    materials: "610*175m",
    materials: "610*175m",
    materials: "914*175m",
    materials: "914*50m"
    }
},
]

Скорее всего я где-то упустил { или }, но вы поняли, что я имел в виду. Возможно я не правильно реализовал базу данных

Answer 1

[Valentin Barbolin]

для mariadb 11

CREATE TEMPORARY TABLE temp_table AS
SELECT printer, 
CONCAT('{', GROUP_CONCAT(QUOTE(materials)), '}') AS materials
FROM printers 
GROUP BY printer;

SELECT CONCAT('{"printer":"', printer, '", "materials":', materials, '}') AS json_format 
FROM temp_table;

DROP TEMPORARY TABLE IF EXISTS temp_table;

Получилось вот так

{"printer":"Принтер цвет", "materials":{'A4','A4color','610*175m','610*50m','914*175m','914*50m','A3','A3color'}}            
{"printer":"Принтер ч/б", "materials":{'A3','A4'}}                                                                           
{"printer":"Принтер ч/б и цвет", "materials":{'610*175m','610*175m','914*175m','914*50m'}}

Comments

[holllop]

Это действительно хорошее решение, но у меня база на PostgreSQL. Если мне поможет твоё решения для моего отмечу решением.

Answer 2

[holllop]

Для базы данных PostgreSQL это будет выглядеть так

WITH temp_table AS (
  SELECT printer, 
         CONCAT('{', STRING_AGG(QUOTE_LITERAL(materials), ','), '}') AS materials
  FROM printers 
  GROUP BY printer
)
SELECT CONCAT('{"printer":"', printer, '", "materials":', materials::json, '}') AS json_format 
FROM temp_table;

В моём случая (я использую Node.js для создания API) это выглядит вот так

const getPrinterMaterials = async () => {
  const query = `WITH temp_table AS (
    SELECT printer, CONCAT('{', STRING_AGG(QUOTE_LITERAL(materials), ','), '}') AS materials
    FROM printer_materials
    GROUP BY printer
  )
  SELECT CONCAT('{"printer":"', printer, '", "materials":', materials::json, '}') AS json_format
  FROM temp_table;`;
  //где-то что-то не так с запросом т.к. получаю ошибку  неверный синтаксис для типа json
  const client = await pool.connect();
  try {
    const result = await client.query(query);
    return result.rows.map(row => row.json_format);
  } finally {
    client.release();
  }
};

module.exports = {
  getPrinterMaterials,
};

<

const express = require('express')
const bodyParser = require('body-parser')
const cors = require('cors')
const db = require('./pool');
const app = express()
const port = 3000

app.use(bodyParser.json())
app.use(
 bodyParser.urlencoded({
    extended: true,
  })
)

app.use(cors())

app.get('/printer_material', db.getPrinterMaterials);

Answer 3

[Rsa97]

Вы неправильно понимаете работу с базой данных. Результат запроса - таблица, в которой каждое поле каждой строки заполнено данными или NULL. Как вы себе представляете желаемую таблицу?
JSON у вас тоже неправильный. В объекте не может быть нескольких свойств с одним именем.
Сделать JSON из таблицы можно. Например, в MySQL для этого используются функции JSON_ARRAYAGG и JSON_OBJECTAGG в сочетании с GROUP BY.

Comments

[holllop]

Хорошо, но я хочу, что бы одному принтеру соответствовало несколько вариантов(можно сказать массив) и не совсем понимаю как это сделать ?

[holllop]

Да, я знаю что JSON можно получить из таблицы, но всё равно спасибо.

[holllop]

Просто как отсеять принтеры по принципу работы я понял я просто добавил ID, если принтер ч/б id=1 если принтер цвет печатает то id=2, если и то и то, тогда id=3 и у меня в списке с вариантами выбора, если человек выбрал ч/б печать, принтеры которые это умеют, будет выбраны c помощью запроса GET с API где была такая команда для вывода

SELECT printer FROM printers WHERE id_work=1 OR id_work=3