Stack Overflow
Stack Overflow

Questions tagged [json-query]

Use this tag for tasks involving queries against a JSON document, such as extracting JSON components from a JSON text. The tag can also be used for query languages intended for specifying such queries.

254 questions
28
votes
5 answers

How to search SQL column containing JSON array

I have a SQL column that has a single JSON array: {"names":["Joe","Fred","Sue"]} Given a search string, how can I use SQL to search for a match in the names array? I am using SQL 2016 and have looked at JSON_QUERY, but don't know how to search for…
paultechguy
  • 2,318
  • 4
  • 25
  • 34
16
votes
2 answers

MySQL nested JSON column search and extract sub JSON

I have a MySQL table authors with columns id, name and published_books. In this, published_books is a JSON column. With sample data, id | name | published_books ----------------------------------------------------------------------- 1 | Tina | …
Kanmaniselvan
  • 522
  • 1
  • 8
  • 23
12
votes
1 answer

Reading json array into rows in SQL Server

Given the sample json data below, how can I write a query to pull the array data all in one step? My goal is to have one row for each item in the ActionRecs array (4). My actual json is more complicated but I think this gives a good example of my…
pretzelb
  • 1,131
  • 2
  • 16
  • 38
9
votes
3 answers

SQL Server 2016 select where in json array

I have a table [JsonTable], and the column [JsonData] save the json string, JsonData like: { "Names": ["John", "Joe", "Sam"] } How can I inner join this table like: SELECT* FROM [TestTable] AS T INNER JOIN [JsonTable] AS J ON T.[Name] IN…
Max
  • 4,439
  • 2
  • 18
  • 32
8
votes
2 answers

how to fix SQL server Json text is not properly formatted. Unexpected character is found at position 151

Im working on a table that has a column in JSON format and I want to extact the coordinate value from that column. So I run the following code: Select *,JSON_VALUE(field,'$."Coordinate-X"[0]') As coordinate INTO TABLE_1 FROM table WHERE…
Zeir
  • 249
  • 1
  • 4
  • 13
8
votes
4 answers

Where clause on json data in Sql Server 2016

I have a nvarchar(1000) field in my table and I am storing JSON data in that column. eg : CONTENT_RULE_ID CONTENT_RULE 1 {"EntityType":"Inquiry", "Values":[1,2]} 2 {"EntityType":"Inquiry", "Values":[1,3]} 3 …
Harisyam M
  • 407
  • 1
  • 3
  • 14
7
votes
1 answer

SQL Server - "for json path" statement does not return more than 2984 lines of JSON string

I'm trying to generate huge amount of data in a complex and nested JSON string using "for json path" statement, and I'm using multiple functions to create different parts of this JSON string, as follow: declare @queue nvarchar(max) select @queue =…
Brian Salehi
  • 414
  • 2
  • 10
  • 19
6
votes
3 answers

mongoexport - issue with JSON query (extended JSON - Invalid JSON input)

I have started learning MongoDB recently. Today the instructor taught us the mongoexport command. While practicing the same, I face a typical issue which none of the other batchmates including the instructor faced. I use MongoDB version 4.2.0 on my…
itsraghz
  • 857
  • 1
  • 11
  • 25
6
votes
4 answers

How I can use Variables in Ansible json_query?

I want to use a ansible variable inside the json query filter. This is my Code: Playbook execution: ansible-playbook debug.yml -e "project_environment=live" - debug: msg: "{{ project_environment }}" - debug: msg: "{{ check_objects |…
bswe
  • 100
  • 1
  • 1
  • 8
6
votes
2 answers

JS Object Query Syntax

I'm trying to find a way filter js collection with a query syntax similar to SQL. The only library I found to accomplish this task is json-query. It works in some cases but it has limitations. It isn't possible to query on different object levels…
Pietro
  • 1,815
  • 2
  • 29
  • 63
6
votes
1 answer

C# Dapper using JSON_VALUE for SQL Server 2016

I want to query data from my table using JSON_VALUE: var str = "123"; var value = "Name" using(var conn = GetMyConnection()) { var result = conn.QueryFirstOrDefault( @"SELECT [Id] FROM [dbo].[MyTable] WHERE…
Max
  • 4,439
  • 2
  • 18
  • 32
5
votes
1 answer

ansible json-query path to select item by content

Does anyone know what json-query filter can be used to select Tigger's food in the sample JSON below? The JSON is a simplified stand-in for a massive and relatively complicated AWS blob. Some background: I was rather pleased to discover that…
Max Murphy
  • 1,701
  • 1
  • 19
  • 29
4
votes
2 answers

How to divide and multiply Ansible facts queried by json_query?

"{{ ansible_facts | json_query('mounts[*].size_available') }} / {{ ansible_facts | json_query('mounts[*].size_total') }} * 100" I am trying to get size available and divide it by size total and Multiply it by 100 to give me the percentage disc…
Ali
  • 55
  • 5
4
votes
2 answers

Filter elements containing string with JMESPath

I want to get a list of addresses of a defined interface type. I found some info here. Here is my playbook: - name: Test JMESPath hosts: localhost gather_facts: no vars: interfaces: - name: em0 address: 10.127.37.89/29 -…
mooky
  • 87
  • 5
4
votes
2 answers

json format query with contains

I have the following json output in ansible: [{ "active_transaction": null, "cores": 4, "hostname": "alpha-auth-wb01" }, { "active_transaction": null, "cores": 4, "hostname": "beta-auth-wb01" }] Now I am trying to filter the…
user6482708
1
2 3
…
16 17