store and query json in mysql table

Question

I am trying to avoid anti-pattern in project where I must allow users to create, modify and delete table fields. So I am looking for storing JSON data in table. For example I have table products:

Products
----------------
id,
user_id,
created,
modified,
price,
_custom <-- there will be stored additional columns for each product needs
        {"adjustment":0.13, "weight":14.60, "have_some_individual_label":"value"}

But I can't see how it is possible to include _column parameters in query. For example, how to query all products where user_id = 1 AND have_some_individual_label = value. Second parameter can be one, ore more (it will be used for filters and analytic). If this is bad approach - what would be better one? Thanks!

eggyal · Accepted Answer · 2012-11-18 13:30:41Z

1

If this is bad approach - what would be better one?

The Entity–attribute–value model model:

CREATE TABLE `ProductInfo` (
  `ProductID` BIGINT UNSIGNED NOT NULL,
  `AttributeKey` VARCHAR(20) NOT NULL,
  `AttributeVal` VARCHAR(20),
  PRIMARY KEY (`ProductID`, `AttributeKey`),
  FOREIGN KEY (`ProductID`) REFERENCES `Products` (`id`)
);

INSERT INTO ProductInfo
  (`ProductID`, `AttributeKey`              , `AttributeVal`)
VALUES
  (         1 , 'adjustment'                ,         '0.13'),
  (         1 , 'weight'                    ,        '14.60'),
  (         1 , 'have_some_individual_label',        'value')
;

answered Nov 18, 2012 at 13:30

eggyal

127k18 gold badges220 silver badges244 bronze badges

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

Comments

px1mp · Accepted Answer · 2012-11-18 13:33:53Z

0

The JSON approach you propose might be usable in case that you only want to compare for equivalence. I'd rather propose to store the extra individual attributes in separate table and link it to the Products table by it's ID.

CREATE TABLE custom_attrs(prodID int, customAttr varchar(32),
       customValue varchar(32));

This might be a better aproach, even though it brings some complexity to the table.

answered Nov 18, 2012 at 13:33

px1mp

5,3821 gold badge21 silver badges10 bronze badges

Collectives™ on Stack Overflow

store and query json in mysql table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related