0

I have single Column entry "Custom Field" and it's contents can grow and shrink. The structure of said column is:

{"10": {"name": "", "type": "Text", "value": ""}, "11": {"name": "", "type": "Text", "value": ""}, "12": {"name": "", "type": "Date", "value": ""}, "13": {"name": "", "type": "Text", "value": ""}, "14": {"name": "", "type": "Date", "value": "2023-02-08 00:00:00"}, "15": {"name": "2:", "type": "Text", "value": ""}, "16": {"name": "3:", "type": "Text", "value": ""}, "17": {"name": "4:", "type": "Text", "value": ""}, "18": {"name": "5:", "type": "Text", "value": ""}}

I would like to find a way to pull the Bolded date "2023-02-08" from the list using the number values "10,11, 12,13,14, etc." This way if I have any data inserted I can delaminate by the assigned position rather than using say "{". The issue I'm concerned about it if there is a value that matches my delaminated value would ruin my query and provide the wrong data to an export. If there is a better way of doing this, please let me know.

Here's the query I'm using right now:

NULLIF(SUBSTRING(SUBSTRING_INDEX(po.customfields,',',-15),62,10), '"}, "15": ') as "Date"

Eric
  • 1
  • 1
  • *The structure of said column is* Do you mean that the whole shown JSON object is a value which is stored in this column in one row? – Akina Feb 09 '22 at 18:16
  • Use JSON_TABLE() function and parse this JSON to separate values. – Akina Feb 09 '22 at 18:17
  • Interesting, I didn't even identify that as JSON (shows how new I am to this), Thanks for the direction with this. I'll check on the JSON_TABLE() Function. And yes, this is an entry in for a single column in a single row. It's how Fishbowl Crams custom fields into MySQL. – Eric Feb 09 '22 at 21:44
  • I'm struggling with this.... I tried json_extract and a few others and have been unsuccessful to pull anything other than the entire json string. Any suggestions? – Eric Feb 09 '22 at 23:59
  • https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=06b289f3cfc2871d0fc2560f6c2721f3 – Akina Feb 10 '22 at 14:45
  • Ok I was able to figure this out. This site was very helpful: https://ubiq.co/database-blog/how-to-query-json-column-in-mysql/ To query the data I needed I need to use the "->'$.value'" or "->>'$.value'" syntax to pull the data out. I believe the ->> includes the whole string example: "Value: 8/10/2022" and the -> just includes the actual value example: "8/10/22" Since the JSON was already a field in the table this method works to query that specific items and include it as a field. – Eric Feb 24 '22 at 16:48
  • *This site was very helpful* [facepalm] Do not read such opuses! The info published in this article contradicts official Reference Manual. Compare the article and [MySQL 8.0 Reference Manual / ... / Functions That Search JSON Values / Operators `->` and `->>`](https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_json-column-path). For the future, read articles on some topic only after you have studied the official documentation on it. – Akina Feb 24 '22 at 18:21

1 Answers1

0

Fishbowl has a customFieldByName function stored

CustomFieldByName(product.customFields,'Category')
Himanshu
  • 31,810
  • 31
  • 111
  • 133
Spenser
  • 1