0

I'm getting the hang of SQL but am struggling with JSON data handling. I just can't get it to work. I'm getting the subject error when trying to run a very basic query, so I'm missing something fundamental.

I've read through many tutorials and this post but I think I need an example using the data I'm working with.

Error

Error Code: 3143. Invalid JSON path expression. The error is around character position 3.

Example JSON Data

{"1": {"name": "Part Source", "type": "Drop-Down List", "value": "Manufactured"}, "28": {"name": "Status", "type": "Drop-Down List", "value": "Current"}}
{"1": {"name": "Part Source", "type": "Drop-Down List", "value": "Manufactured"}, "28": {"name": "Status", "type": "Drop-Down List", "value": "Current"}}
{"1": {"name": "Part Source", "type": "Drop-Down List", "value": "Manufactured"}, "28": {"name": "Status", "type": "Drop-Down List", "value": "Current"}}

The data resides in a table named part in a column named customFields.

I've tried many things but to start simple, below is my attempt to extract the name from the first nested key pair. This yields the above error.

SELECT customFields->>'$.1.name'

FROM part;

I also tried with this the positional [0] and [1] after $.1.name with the same result.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Tim
  • 5
  • 5
  • That's not JSON data, that's a *series* of JSON documents. A JSON field should contain *one* JSON document and one only. If these were in an array like `[ { ... }, { ... }, ... ]` it'd be fine. – tadman Jan 10 '23 at 19:45
  • Tip: Use `jsonb` which rejects invalid JSON when inserting. – tadman Jan 10 '23 at 19:46
  • 1
    The JSON data is not the problem. The error says that the problem is the JSON _path_ in your extract expression: `'$.1.name'` needs to be `'$."1".name'` because path elements that start with digits need to be double-quoted. – Bill Karwin Jan 10 '23 at 20:13

0 Answers0