Converting table records into json record

I have a table like this.

CREATE TABLE items (
item BIGINT,
price BIGINT
);

How to convert all records in this table int to json_record format as below.

{‘item’:1,‘price’:20},
{‘item’:2,‘price’:30},
{‘item’:3,‘price’:40}
(…)

This is coming in the upcoming 7.1 release shipping in May. This will run as
select to_json(*) from items;

How to meet this requirement in 7.0?

Either store in json or convert to json in the app.

Hi @rjsv! Which client library are you using to connect and query MemSQL? Many drivers and languages make it very easy to convert each row into a JSON object. I assume you are moving data from the database to a Javascript frontend, hence the conversion requirement?

Incase if we find any duplicate record in database , we would like to delete existing record and insert the new record. Before deleting the matched record, we would like to capture the matched record into a common table. (If it json we can do it it in one table. Otherwise we need to create a separate table to each and ever table.)

Oh I see, that’s a very interesting use-case! It absolutely makes sense that for this case you will need something like to_json. In the meantime, would it be possible to round-trip the rows through a job-worker of some kind to convert each row to JSON and push it into your common table?

Another option which you might consider is adding a couple metadata columns to each table. One strategy is to add a deleted-at column and setting it to NOW() instead of deleting the row. If you need a primary or unique key on the table, you can add a row-version column as well which represents the “version” of the row in question. It’s a bit more complex to organize, but it gives you a lot of advantages when you want to look at the history of each row as well as possibly doing a rollback. If you go down this approach check out some of our other helpful query shapes such as window functions and CTE’s. These can be very useful tools to help you analyze row history and keep queries clean.

Another feature we are excited to build in the future is global versioning which was hinted at in our most recent blog post: https://www.memsql.com/blog/the-future-is-bottomless/ This would possibly simplify what you are trying to do by effectively giving you the ability to time-travel through any database state.

Is a great article, when do you expect it will be available as part of MemSQL?

At the end of the year. We are working on it as fast as we possibly can.